Ask a new question

Need to calculate man hour rate in Excel 2010

I own a tree service and I’m trying to get the man hour rate for a job that takestwo days. Basically, you divide the total job price by the total man hours. The cell layout is as follows:

Crew

Crew Hrs

Price

Man Hr

2

5.5

    

2

15.0

    
   

$2,995

?

 

What’s the formula to calculate the man hour rate?

Answer
Answer

I own a tree service and I’m trying to get the man hour rate for a job that takestwo days. Basically, you divide the total job price by the total man hours. The cell layout is as follows:

Crew

Crew Hrs

Price

Man Hr

2

5.5

    

2

15.0

    
   

$2,995

?

 

What’s the formula to calculate the man hour rate?

For this simple case, you could write in D4:

=ROUND(C4/(A2*B2+A3*B3),2)

But more generally:

=ROUND(C4/SUMPRODUCT(A2:A3,B2:B3),2)

I assume the crew numbers are in A2:A3, the corresponding crew hours are in B2:B3, and the total job price is in C4.

Note that D4*SUMPRODUCT(A2:A3,B2:B3) might not exactly equal C4.  It might be higher or lower.  If you want ensure that it is lower, change ROUND to ROUNDDOWN above.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 22, 2024 Views 3,489 Applies to: