Forum Discussion
Solved
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 appr...
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 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.
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
mtarler Thank you. I missed that. Do all the other formulas look correct? Should I use xlookup instead of vlookup?
Thanks