Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-848741%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848741%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20create%20a%20formula%20where%20I%20need%20to%20calculate%20the%20rate%20based%20on%20what%20type%20of%20person%20worked%20and%20the%20number%20of%20hours%20worked%2C%20but%20there%20is%20a%20regular%20time%20and%20and%20overtime%20rate.%26nbsp%3B%20I%20have%20this%20one%20which%20calculates%20the%20regular%20rate.%26nbsp%3B%20But%20I%20can't%20figure%20out%20how%20to%20get%20the%20over%20time%20rate%20added%20to%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(%24J3%3D%22Foreman%22%2C%24K3%26lt%3B9)%2C(%24K3*85)*OR(%24J3%3D%22Labourer%22%2C%24K3%26lt%3B9)%2C(%24K3*80))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20ideally%2C%20I%20would%20like%20the%20regular%20rate%20to%20appear%20into%20column%20L%20and%20the%20OT%20rate%20to%20appear%20in%20column%20M.%26nbsp%3B%20Is%20that%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-848741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-850621%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-850621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407231%22%20target%3D%22_blank%22%3E%40Bev_JRCapital%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20building%20a%20formula%20using%20'hard-coded'%20employee%20types%20and%20hourly%20rates%2C%20create%20a%20table%20with%20the%20applicable%20rates%20for%20each%20employee%20type.%20From%20that%20table%2C%20you%20can%20lookup%20the%20corresponding%20rates%20for%20each%20person%20based%20on%20employee%20type.%20I%20suggest%20you%20insert%20a%20column%20that%20calculates%20the%20number%20of%20hours%20subject%20to%20OT.%20That%20way%2C%20you%20can%20easily%20calculate%20regular%20pay%20as%20%22total%20hours%22%20minus%20%22OT%20hours%22%20X%20regular%20rate.%20OT%20pay%20is%20calculated%20as%20OT%20hours%20X%20OT%20rate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20small%20example.%20The%20greyed%20area%20contains%20the%20formulas%20I%20described%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20solution%20allows%20you%20to%20add%2Fchange%20employee%20types%20or%20change%20rates%20without%20having%20to%20edit%20all%20your%20formulas.%3C%2FP%3E%3CP%3EI%20hope%20you%20find%20this%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bev_JRCapital
Occasional Visitor

I have to create a formula where I need to calculate the rate based on what type of person worked and the number of hours worked, but there is a regular time and and overtime rate.  I have this one which calculates the regular rate.  But I can't figure out how to get the over time rate added to that.

 

=IF(AND($J3="Foreman",$K3<9),($K3*85)*OR($J3="Labourer",$K3<9),($K3*80))

 

And ideally, I would like the regular rate to appear into column L and the OT rate to appear in column M.  Is that possible?

1 Reply

@Bev_JRCapital 

Rather than building a formula using 'hard-coded' employee types and hourly rates, create a table with the applicable rates for each employee type. From that table, you can lookup the corresponding rates for each person based on employee type. I suggest you insert a column that calculates the number of hours subject to OT. That way, you can easily calculate regular pay as "total hours" minus "OT hours" X regular rate. OT pay is calculated as OT hours X OT rate.

 

Attached a small example. The greyed area contains the formulas I described above.

 

Hope this helps.

 

This solution allows you to add/change employee types or change rates without having to edit all your formulas.

I hope you find this useful.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies