 • 409K Members
• 11.8K Online
• 466K Conversations

Excel formula help

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?

Re: Excel formula help

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