Help with Budget sheet, with different rates depending on hours
Good afternoon, I'm looking for help with my budget sheet.
I need to calculate the project design cost using a rate, but when we have a change order the rate can be different.
As an example for the first 50 hours I need to use the rate of $100, but then for the next 50 hours I need to use the rate of $110, and so on. I have to formula working correctly at that point, I'm stuck when we go over Budgeted hours, so in my example when I put 201 in Actual the cost goes blank
=IF(Actual@row <= Budget@row, Actual@row * Budget2, IF(AND(Actual@row > Budget@row, Actual@row <= SUM(Budget@row:[CO1 Budget]@row), [CO1 Budget]@row > 0), Budget@row * Budget2 + (Actual@row  Budget@row) * [CO1 Budget]2,
IF(AND(Actual@row > SUM(Budget@row:[CO1 Budget]@row), Actual@row <= SUM(Budget@row:[CO2 Budget]@row), [CO2 Budget]@row > 0), Budget@row * Budget2 + [CO1 Budget]@row * [CO1 Budget]2 + (Actual@row  SUM(Budget@row:[CO1 Budget]@row)) * [CO2 Budget]2,
IF(AND(Actual@row > SUM(Budget@row:[CO2 Budget]@row), Actual@row <= SUM(Budget@row:[CO3 Budget]@row), [CO3 Budget]@row > 0), Budget@row * Budget2 + [CO1 Budget]@row * [CO1 Budget]2 + [CO2 Budget]@row * [CO2 Budget]2 + (Actual@row  SUM(Budget@row:[CO2 Budget]@row)) * [CO3 Budget]2, ""))))
Answers

What would you like the result to be if Actual exceeds the total of the 4 budgets? At the moment the formula does nothing in that situation (therefore returns blank).
If everything above 150 is charged at $130 you could simply remove the Actual@row <= SUM(Budget@row:[CO3 Budget]@row) part of the formula.

That worked, the only problem now is not all budget sheets have multiple budgets. I would like to keep the formula the same on all budget sheets.
So for this example, I could have the first two columns filled in but as soon as I go over the 100 actual mark it doesn't calculate correctly.
Thank you for the help

Hi,
This part of your formula is checking there is an amount in the budget column
[CO3 Budget]@row > 0
When this is not true nothing is returned. You can replace the nothing with another IF formula.
Help Article Resources
Categories
Check out the Formula Handbook template!