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