I am working on a payroll assignment for school and after a couple of hours of working on it, I am still having troubles getting my gross wage formula to work correctly. This is what I have so far.
The problem states that a minimum of 40 hours is payed to each employee regardless of how many hours they worked. 40-48 hours is paid 2 times the regular hourly wage. Over 48 is paid the OT rate plus $50 for every hour over 48. (I have not tried figuring out this last step in the problem yet because I can't get the 2nd step to work.) Here is what I have for my formula thus far.
=IF(I2<=40,40*H2,IF(AND(I2>40,I2<=48),I2*H2,(I2-48*'Payroll Assumptions'!B4+40*I2)))
I2= Hours worked
H2= Hourly Wage
Payroll Assumptions B4= Overtime rate
The formula correctly calculates the wages for employees under 40 hours, however when there is OT to be calculated, it is wrong. Maybe it is something very simple that I am overlooking because I have been staring at this problem for to long. Any help or advice would be greatly appreciated!
The problem states that a minimum of 40 hours is payed to each employee regardless of how many hours they worked. 40-48 hours is paid 2 times the regular hourly wage. Over 48 is paid the OT rate plus $50 for every hour over 48. (I have not tried figuring out this last step in the problem yet because I can't get the 2nd step to work.) Here is what I have for my formula thus far.
=IF(I2<=40,40*H2,IF(AND(I2>40,I2<=48),I2*H2,(I2-48*'Payroll Assumptions'!B4+40*I2)))
I2= Hours worked
H2= Hourly Wage
Payroll Assumptions B4= Overtime rate
The formula correctly calculates the wages for employees under 40 hours, however when there is OT to be calculated, it is wrong. Maybe it is something very simple that I am overlooking because I have been staring at this problem for to long. Any help or advice would be greatly appreciated!
