Gross Pay

[Butter7017]

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

[#2]

@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.

[#3 · Accepted solution]

@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.

[#4]

@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.  

[#5]

@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.

[#6]

@mtarler 

Also the extra pay is just a fixed amount the employee gets for extra weekend work.

[#7]

@Butter7017  Your formula has ...D2+0.05*MAX... when it should be 0.5

[#8]

@mtarler Thank you.  I missed that.  Do all the other formulas look correct?  Should I use xlookup instead of vlookup?

Thanks