Sep 15 2019 01:23 PM
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
Sep 15 2019 03:19 PM
Solution
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))
Sep 16 2019 12:31 AM
Sep 16 2019 09:01 AM
@Ingeborg Hawighorst Thank you so much for the help on this!
Sep 16 2019 09:21 AM
@Sergei Baklan Thanks for this variant and your assistance on this one!
Sep 16 2019 11:10 AM
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.
Sep 15 2019 03:19 PM
Solution
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))