Formula Question

[JDT]

Formula Question

Example:

Name Result $ Hours/Minutes

Jane +100 1:00
Bob -50 2:50
Bob +125 3:12
Ed +325 7:45
Jane +50 :55
Ed -150 1:37
Jane -25 2:50

Jane earned $125 in 4 hours and 45 minutes. How do I write a formula
in a single cell to determine Jane's hourly rate that will
automatically update as more data is added?

Thanks.

 

[Dave Peterson]

=SUMIF(A2:A100,"Jane",B2:B100)/SUMIF(A2:A100,"jane",C2:C100)/24

Format as General (or some number)

And make that range large enough to accept all your entries.

(or even use the whole column???

=SUMIF(A:A,"Jane",B:B)/SUMIF(A:A,"jane",C:C)/24


You may want to look at data|pivottable to do this. You can insert a field
that calculates based on the (total dollars/total time /24)

 

[Nick Hodge]

JDT

You will need to enter the names down a column somewhere (I've presumed the
word Jane is in E2) and then presuming you are using columns A:C as your
data. in F2 enter

=SUMIF(A:A,$E$2,B:B)/(SUMIF(A:A,$E$2,C:C)*24)

And copy down as far as the list of names go. If you wanted to list the
individual names in the formula then you could change the above formula to

=SUMIF(A:A,"Jane",B:B)/(SUMIF(A:A,"Jane",C:C)*24)

But IMHO that limits the flexibility of having a separate list that you can
add to

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)