Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

How to calculate average hourly pay rate

Posted by Arnie on August 10, 2001 4:18 PM
Hello,
If I have a full time employee that earns at the rate of $20.00 per hour and I consider him at 1 FTE and I have another employee that earns at the rate of $16.50 per hour and I consider him to be a .5 FTE. How would I come up with a formula in Excel to calculate this. Any help would be much appreciated...

Thanks


Check out our Excel Resources

Re: How to calculate average hourly pay rate

Posted by Arnie on August 10, 2001 4:20 PM



Re: How to calculate average hourly pay rate

Posted by Joe Was on August 11, 2001 4:57 AM
Cost per worker hr. is ($20.00 + ($16.50 x 0.5))

To calculate the Avg. hr. rate you need the number of hrs. worked by each for any given period.

If name is col.A, salary is col.B, hrs col.C and a days pay is col.D, Then:

=SUM(Dy:Dx)/SUM(Cy:Cx)
where D = [=Bx*Cx]

x and y are ranges or cells.

The trick is to get a days pay for each worker first. then add [SUM] all the days pay for the period you want and devide it by the total hours worked. If you do this for each worker, to give you more data, then add each worker average and devide by the number of workers. It will work if you do all the data for all workers at once as well. JSW


=SUM(D1:D2)/SUM(C1:C2)



average hourly pay rate--be careful

Posted by Loren on August 13, 2001 5:07 AM
IMHO, it is not accurate to "average the averages" to
get your overall average. Instead add all the wages and
all the hours, then divide.



Not what I was asking about

Posted by TeeJay on August 13, 2001 7:22 PM
I was asking how I could sort by the number of total hours. I never mentioned pay rate at all. But I did get my question answered (sort of) and I really appreciate the help. Thanks for your input everyone.

TJ


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.