SOLVED

IF Function to calculate multiple OT rates

Copper Contributor

I am struggling to write an IF formula that will calculate the gross pay based upon the following information:

Dept. 1 employees receive straight time for 40 hours or less and double time for hours over 40.

Dept. 2 employees receive straight time for 40 hours or less and triple time for hours over 40.

Name   Dept.   Rate   Hours   Gross Pay

Sally         1       $10      20

Joe           2       $10      50

Jill            1       $20      44

 

 

5 Replies
best response confirmed by maynardc (Copper Contributor)
Solution

@maynardc 

 

With the first name in cell A2, put this formula in cell E2 and copy down:

 

=IF(D2<=40,D2*C2,(40*C2)+((D2-40)*IF(B2=1,2,3)*C2))

 

2019-09-16_10-18-47.png

@maynardc 

Another variant for E2

image.png

is

=(MIN(40,$D2)+MAX(0,$D2-40)*(2+($B2=2)))*$C2

@Ingeborg Hawighorst Thank you so much for the help on this!

@Sergei Baklan Thanks for this variant and your assistance on this one!

@maynardc 

You are welcome. I added another variant only to illustrate that in Excel practically any task could be done by several ways. Which one to use - it depends. But in most cases better to select formulas with which you are more familiar and which you understand better. It will be easier in maintenance.

1 best response

Accepted Solutions
best response confirmed by maynardc (Copper Contributor)
Solution

@maynardc 

 

With the first name in cell A2, put this formula in cell E2 and copy down:

 

=IF(D2<=40,D2*C2,(40*C2)+((D2-40)*IF(B2=1,2,3)*C2))

 

2019-09-16_10-18-47.png

View solution in original post