# Calculating hourly rate to charge customer for a line item based on RT, OT, DT rates

1. ## Calculating hourly rate to charge customer for a line item based on RT, OT, DT rates

Hi there! I am having an issue with figuring out the formula to calculate a rate to charge our customer by calculating X number of crew on site x Amount of Hours x Amount of Days and calculate the rate based on regular hours, overtime hours, Double time hours and add a markup of any percentage to it. The only way I was able to do it was calculate it by 10%, 20% or 30% markup only but we would like to just enter any percentage in Column A.

This is what I came up with and works but only for the rates I mentioned:
=IF(A2=0.1,IF(C2<=8,81,IF(C2<=10,87,IF(C2<=12,91))),IF(A2=0.2,IF(C2<=8,88,IF(C2<=10,94,IF(C2<=12,98))),IF(C2<=8,95,IF(C2<=10,101,IF(C2<=12,105)))))

Also, we need it to calculate based on all hours over 12 hours as well (13, 16, etc.).

I attached a copy of the worksheet and I would greatly appreciate any advice/instruction you can offer. Thank you so much for taking the time to review this.

2. ## Re: Calculating hourly rate to charge customer for a line item based on RT, OT, DT rates

In the attached I adjusted the lookup table to accept hour ranges 1 to 12 and greater. Also made the percentages real percentages.

 I J K L 1 10% 20% 30% 2 1 \$ 81.00 \$ 88.00 \$ 95.00 3 10 \$ 87.00 \$ 94.00 \$ 101.00 4 12 \$ 91.00 \$ 98.00 \$ 105.00

Then in F2 and filled down. This returns the same as your longer formula without returns for 0 hours.
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Calculating hourly rate to charge customer for a line item based on RT, OT, DT rates

Thank you again for your help.

As it came down to it, I was simply overthinking it. After reviewing what you sent me, I took a step back and reviewed again and
I came up with the attached, which is exactly what I needed.

=IF(F2,IF(D2<=8,81,IF(D2<12,87,IF(D2>=12,91))))

4. ## Re: Calculating hourly rate to charge customer for a line item based on RT, OT, DT rates

Good deal.

There are currently 1 users browsing this thread. (0 members and 1 guests)