• 549K Members
• 6,928 Online
• 657K Conversations

## Excel formula help

Highlighted
Occasional Visitor

# 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?

Highlighted

# 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
Excel Help - Stacking IF Formulas