MS Excel Help

[Krogoth]

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 

[attachment deleted by admin]

[Hawkes]

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.

[Krogoth]

so copy one cell, then paste it into all the others?

[Hawkes]

Correct

[Krogoth]

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

[Krogoth]

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?

[Hawkes]

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....

[Hawkes]

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.

[Krogoth]

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)

[Hawkes]

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.