gfxHomeForumHelpLoginRegistergfx
gfxgfx
      « previous next »
Pages: [1] Go Down Print
Author Topic: MS Excel Help  (Read 3763 times)
Krogoth
Has a PS2 Hardon
WoW Member
Hero Member
***

Karma: +6/-0
Offline Offline

Posts: 709


« on: July 21, 2006, 04:48:57 AM »

I think I've shown this to Hurley a long, long time ago, but he had no clue how to help.

I've been tracking my stats every time I play Planetside since just after merits came out (I'm a severe stat whore in everything, Every week I'm anxious to see what my Items per Hour statistic is every week at WalMart). I've been looking for a way to automate more of it instead of doing it by hand (if you look at page 2, all my BEP per Hour, CEP per hour, etc.). I've figured out how to use some of the formulas, but cant for the life of me figure out how to get an entire column to do a small formula that exactly the same but one row number greater (ie: for row 2, BEP per hour would be "=F2/B2", and row 3 would be  "=F3/B3", but I have to enter each and every one by hand). Can anyone decent with Excel gimme a hand with this?

And go ahead and laugh at my stats, nothing is consistant  Tongue

[attachment deleted by admin]
Logged

Hawkes
Wolverine Council
Administrator
Hero Member
*****

Karma: +52/-7
Offline Offline

Posts: 2157



WWW
« Reply #1 on: July 27, 2006, 01:39:41 AM »

Hmm... I would think just a simple copy and then highlight all the cells and paste.  It should bump the row up 1 each time.
Logged

Krogoth
Has a PS2 Hardon
WoW Member
Hero Member
***

Karma: +6/-0
Offline Offline

Posts: 709


« Reply #2 on: July 27, 2006, 02:48:44 AM »

so copy one cell, then paste it into all the others?
« Last Edit: July 27, 2006, 05:34:25 AM by Krogoth » Logged

Hawkes
Wolverine Council
Administrator
Hero Member
*****

Karma: +52/-7
Offline Offline

Posts: 2157



WWW
« Reply #3 on: July 27, 2006, 02:27:29 PM »

Correct
Logged

Krogoth
Has a PS2 Hardon
WoW Member
Hero Member
***

Karma: +6/-0
Offline Offline

Posts: 709


« Reply #4 on: July 27, 2006, 06:27:03 PM »

well I'mm be damned, the man knows what hes doing, thanks
Logged

Krogoth
Has a PS2 Hardon
WoW Member
Hero Member
***

Karma: +6/-0
Offline Offline

Posts: 709


« Reply #5 on: July 27, 2006, 07:26:49 PM »

aha, and now problem #2 arises. in trying to format the "BEP per hour" and "CEP per hour" columns, I have run into a little hitch. It attempts to divide the BEP by the numbers in the hour column, not by the amount of time like it should.

Example:

in row 2, BEP is 14286, and time played is 3:56. Excel attempts to divide 14286 by, of all absured numbers, ~0.6 and gives an answer of  19:36 (auto put into time format) or 87168.81......wtf is going wrong here?
Logged

Hawkes
Wolverine Council
Administrator
Hero Member
*****

Karma: +52/-7
Offline Offline

Posts: 2157



WWW
« Reply #6 on: July 27, 2006, 08:03:03 PM »

Well you are trying to divide an integer by time.  You're going to have to use some of the time functions in excel to do that math or you could write your own.... (min*60)+sec.  Something like that.

Now keep in mind that when you input 3:56 in to the cell, it thinks you are entering 3:56:00 am.... so let's see... you need to convert that to min:sec.  Let me toy around with this a bit......i'll be back....
Logged

Hawkes
Wolverine Council
Administrator
Hero Member
*****

Karma: +52/-7
Offline Offline

Posts: 2157



WWW
« Reply #7 on: July 27, 2006, 08:36:32 PM »

OK Kronads... here it is....

Column A is BEP
Column B is time formatted in hours:mins
Column C =A1/(B1*24)

format column c as a number with no decimal places... in your case it is as such

_______________________________
|  14286  |  3:56  |  =A1/(B1*24)  |
_______________________________


Sorry for the shitty formatting, but you get the point.  after you enter the formula in column C your answer should be 3632 BEP per hour.  Copy and paste like the other cells and it will automatically increment the row for you.
Logged

Krogoth
Has a PS2 Hardon
WoW Member
Hero Member
***

Karma: +6/-0
Offline Offline

Posts: 709


« Reply #8 on: July 27, 2006, 08:56:57 PM »

damn, thanks hawkes. This sure as hell beats doing all the calculations out by hand (concidering a good portion of them I was wrong with :-p)
Logged

Hawkes
Wolverine Council
Administrator
Hero Member
*****

Karma: +52/-7
Offline Offline

Posts: 2157



WWW
« Reply #9 on: July 28, 2006, 12:31:06 AM »

No problem bro... I've done quite a bit of stuff in Excel... just takes a few minutes to come back... you know...old age and all.
Logged

gfx
Pages: [1] Go Up Print 
gfx
Jump to:  
gfx
Powered by SMF 1.1.21 | SMF © 2015, Simple Machines
Helios / TinyPortal v0.9.8 © Bloc
gfx
Powered by MySQL Powered by PHP Valid XHTML 1.0! Valid CSS!
fmclip.com