Forum Discussion
Gross Pay
I am trying to setup a spreadsheet to calculate pay. I have attached what I am working on but could use some expert help making sure I am doing it the best possible way. Any help would be much appreciated.
Thanks
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.
7 Replies
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.
Also the extra pay is just a fixed amount the employee gets for extra weekend work.
mtarler Thanks for the help
I have taken your suggestions and tried to incorporate them but am having an issue getting gross pay to calculate correctly. I used vlookup instead of xlookup not sure if that makes the difference.
Thanks for your help.
Butter7017 Your formula has ...D2+0.05*MAX... when it should be 0.5
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.
