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

Find more posts tagged with

Formulas

Accepted answers

All comments

KPH

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.

SAnkney

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

KPH

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.