Calculating pay help

[wolf359]

I am trying to calculate the total pay for an employee who receives regular time for 40 hours, time and a half for 40-50 hours and double-time for hours over 50.

I have figured out a way to do this, but first off it is complicated and second if i but in something less that 40 hours, nothing works right.

Is there an easier way.

 

[jmthompson]

If hours worked are in A, base pay rate is in B and pay is in C:

=IF(A2>50,((A2-50)*(2*B2))+(10*(1.5*B2)+40*B2),IF(A2>40,((A2-40)*(1.5*B2)+40*B2),A2*B2))

 

[Ralajer]

Welcome to the board. Here is another approach to solve the problem that will work.
A is hours
B is the standard rate

=IF(A2<=40,A2,IF(A2>50,55+2*MOD(A2,50),40+1.5*MOD(A2,40)))*B2

It scales the hours instead of the rate which is mathematically equivalent to scaling the rate.

 

[Desu Nota from Columbus]

For Excel 2007, i think this works (didn't verify): Hours in A2, pay rate per hour in b1

=
Didn't refresh page to see responses: use jmthompson's.

 

[shg]

Another way:

=Rate * SUMPRODUCT((A1>{0,40,50})*(A1-{0,40,50})*{1,0.5,0.5})

 

[markmzz]

Wolf359,

Assuming that the column data in hours worked (A) are in Excel hour format, use the following formula:

=24*A2*B2+IF(A2*24<=40,0,(24*A2-40)*1.5+IF(A2*24<=50,0,(24*A2-50)*2))

Markmzz