Oct 14 2020 08:31 AM
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
Oct 14 2020 08:41 AM
@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.
Oct 14 2020 09:00 AM
Solution@Butter7017 Your set up is fine except your formula for 'gross pay' has absolute values instead of cell references.
that said some recommendations:
Oct 14 2020 09:08 AM
@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.
Oct 14 2020 09:42 AM
@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.
Oct 14 2020 09:42 AM
Also the extra pay is just a fixed amount the employee gets for extra weekend work.
Oct 14 2020 09:50 AM
@Butter7017 Your formula has ...D2+0.05*MAX... when it should be 0.5
Oct 14 2020 09:53 AM
@mtarler Thank you. I missed that. Do all the other formulas look correct? Should I use xlookup instead of vlookup?
Thanks
Oct 14 2020 09:00 AM
Solution@Butter7017 Your set up is fine except your formula for 'gross pay' has absolute values instead of cell references.
that said some recommendations: