SOLVED

Gross Pay

%3CLINGO-SUB%20id%3D%22lingo-sub-1780027%22%20slang%3D%22en-US%22%3EGross%20Pay%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1780027%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20setup%20a%20spreadsheet%20to%20calculate%20pay.%26nbsp%3B%20I%20have%20attached%20what%20I%20am%20working%20on%20but%20could%20use%20some%20expert%20help%20making%20sure%20I%20am%20doing%20it%20the%20best%20possible%20way.%26nbsp%3B%20Any%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1780027%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1780108%22%20slang%3D%22en-US%22%3ERe%3A%20Gross%20Pay%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1780108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832289%22%20target%3D%22_blank%22%3E%40Butter7017%3C%2FA%3E%26nbsp%3BYou%20share%20a%20very%20small%20example%2C%20but%20it'll%20do.%20I%20guess%20you%20want%20to%20automate%20the%20extra%20pay%20a%20bit%20as%20well.%20Figured%20out%20that%20hours%20between%2040%2B%20and%2060%20hours%20are%20paid%20at%20150%25.%20Perhaps%20the%20attached%20can%20inspire%20you%20to%20automate%20it%20even%20further.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1780252%22%20slang%3D%22en-US%22%3ERe%3A%20Gross%20Pay%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1780252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20have%20to%20assume%20the%20fact%20that%20the%20'Extra%20pay'%20is%20150%25%20*%20OT%20hrs%20is%20just%20a%20coincidence%20in%20this%20case%20because%20it%20looks%20like%20the%20formula%20for%20gross%20pay%20takes%20reg%20hrs%20*%20pay%20rate%20%2B%20OT%20hrs%20*%20150%25%20*%20pay%20rate%20%2B%20Extra%20pay%2C%20which%20would%20mean%20the%20OT%20rate%20would%20be%20300%25.%26nbsp%3B%20So%20if%20that%20'Extra%20pay'%20is%20intended%20as%20a%20way%20to%20calculate%20the%20OT%20pay%20amount%20then%20the%20gross%20pay%20equation%20should%20be%20corrected.%26nbsp%3B%20I%20assume%20it%20is%20instead%20just%20a%20coincidence%20and%20is%20truly%20an%20'Extra%20pay'%20for%20bonuses%20and%20other%20stuff.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

7 Replies
Highlighted

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

 

Highlighted
Best Response confirmed by Butter7017 (New Contributor)
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.
Highlighted

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

Highlighted

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

Highlighted

@mtarler 

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

Highlighted

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

 

Highlighted

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

 

Thanks