Forum Discussion
Gross Pay
Butter7017 Your set up is fine except your formula for 'gross pay' has absolute values instead of cell references.
that said some recommendations:
- you should add some sort of date and/or pay period
- create another sheet with employee and pay rate and use lookup to get it; unless that pay rate changes and you need to track it
- do you need to break out regular hours and OT hours? because you could just incorporate that directly into the gross pay equation
- although really trivial and more just personal preference; the formulas for reg hours could be =MIN(D3,40) and OT hours =MAX(D3-40,0)
- again trivial/personal preference: the formula for gross pay could be =C3*(E3 + 1.5 * F3) + G3
note if you use the above recommendations it might be =XLOOKUP(B3,'EmployeePaySheet'!$A:$B,2)*(D3+0.5*MAX(D3-40,0)) + G3
where the employee name & pay rate is on a new sheet called 'EmployeePaySheet' in columns A:B and assuming the same 50% bonus on OT hours over 40 hours.
Butter7017 You share a very small example, but it'll do. I guess you want to automate the extra pay a bit as well. Figured out that hours between 40+ and 60 hours are paid at 150%. Perhaps the attached can inspire you to automate it even further.
Riny_van_Eekelen I have to assume the fact that the 'Extra pay' is 150% * OT hrs is just a coincidence in this case because it looks like the formula for gross pay takes reg hrs * pay rate + OT hrs * 150% * pay rate + Extra pay, which would mean the OT rate would be 300%. So if that 'Extra pay' is intended as a way to calculate the OT pay amount then the gross pay equation should be corrected. I assume it is instead just a coincidence and is truly an 'Extra pay' for bonuses and other stuff.