# Help with Budget sheet, with different rates depending on hours

Options
✭✭✭

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, ""))))

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!