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

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

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)

Posted by Loren on August 13, 2001 5:07 AM

average hourly pay rate--be careful

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.

Posted by TeeJay on August 13, 2001 7:22 PM

Not what I was asking about

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.