Forum Discussion
IF Function to calculate multiple OT rates
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
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))
5 Replies
SergeiBaklan Thanks for this variant and your assistance on this one!
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.
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))
IngeborgHawighorst Thank you so much for the help on this!
