Need to work hours backwards

[Susan]

I need to calculate hourly rate by a commission amount so I need to work
backwards. Gross pay of $1000 and 45 hours total worked. 5 of those hours
are overtime at time and half. I need to know what the actual hourly rate of
pay would be. I am not sure what function to begin with. Can anyone help
Thanks

 

[Jim Thomlinson]

More of an algebra question than an XL question.

40r + 5*1.5r = 1000
so
r(40 + 5*1.5) = 1000
r = 1000/(40 + 5*1.5)
r = 1000/47.5
r = 21.05

 

[Dave]

Hi,

If your hours are in A1 and your gross pay in B1, put this into B2:

=IF(A1>40,B1/((A1-40)*1.5+40),B1/A1)

Regards - Dave.

 

[BoniM]

=B2/IF(C2>40,40+((C2-40)*1.5),B2/C2)
If salary is in B2 and total hours in C2.
Divides salary by hours worked if none are over 40.
If over 40, multiplies overtime times 1.5 and then adds it to 40 and then
divides salary by that.

 

[BoniM]

Correction:
=B2/IF(C2>40,40+((C2-40)*1.5),C2)

 

[Susan]

Your right! being that I flunked algebra twice many many moons ago is why I
could not figure the formula out. This will help a lot. thank you for
your help

 

[Susan]

Thank you for the response. I will give this a try and let you know. I knew
I needed an IF just could not figure out the math
Susan

 

[Susan]

Thank you for responding. I am going to try this one and the other one and
see what works. I appreciate your taking the time to respond. I will let
you know how it works out

Susan

 

[Susan]

Thanks Dave,
Worked perfectly

 

[Susan]

Works Beautifully - Thank you

Correction:
=B2/IF(C2>40,40+((C2-40)*1.5),C2)

 

[BoniM]

Happy to help!

Works Beautifully - Thank you