| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to work hours backwards
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
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 -- HTH... Jim Thomlinson "Susan" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
Hi,
If your hours are in A1 and your gross pay in B1, put this into B2: =IF(A140,B1/((A1-40)*1.5+40),B1/A1) Regards - Dave. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
=B2/IF(C240,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. "Susan" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
Correction:
=B2/IF(C240,40+((C2-40)*1.5),C2) "Susan" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Susan" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
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 "Dave" wrote: Hi, If your hours are in A1 and your gross pay in B1, put this into B2: =IF(A140,B1/((A1-40)*1.5+40),B1/A1) Regards - Dave. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
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 "BoniM" wrote: Correction: =B2/IF(C240,40+((C2-40)*1.5),C2) "Susan" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
Thanks Dave,
Worked perfectly "Dave" wrote: Hi, If your hours are in A1 and your gross pay in B1, put this into B2: =IF(A140,B1/((A1-40)*1.5+40),B1/A1) Regards - Dave. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
Works Beautifully - Thank you
"BoniM" wrote: Correction: =B2/IF(C240,40+((C2-40)*1.5),C2) "Susan" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to work hours backwards
Happy to help!
"Susan" wrote: Works Beautifully - Thank you "BoniM" wrote: Correction: =B2/IF(C240,40+((C2-40)*1.5),C2) "Susan" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating hours work has taken | Excel Discussion (Misc queries) | |||
| Work Hours | Excel Worksheet Functions | |||
| Work hours calculation | Excel Discussion (Misc queries) | |||
| Work out overtime hours for individuals | New Users to Excel | |||
| Can you work this backwards | Excel Discussion (Misc queries) | |||
Linear Mode
