Issues combining 2 formulas

I have the following columns: Expected Budget, Director Approval, VP Approval, Approved Projects.

The conditions being, if Expected Budget is less than $10000, only director approval required, if more than $10000, both director and VP approval is required.

On the Approved Projects, I would like to show if the project is finally approved to proceed base on the condition above.

I have worked out the formula separately and it works:

1) =IF(AND([Expected Budget]@row < 10000, ([Director approval]@row = "Approved")), "Approved", "Declined")

(as appear in row 1 in picture below)

2) =IF(AND([Expected Budget]@row >= 10000, [Director approval]@row = "Approved", [VP approval]@row = "Approved"), "Approved", "Declined")

(as appear in row 2 in picture below)

But I am unable to combine them. (as appear in row 3 in picture below)

And if possible, I also would like to add another rule: If approval is needed and "submitted" is shown, display "waiting for approval"

Appreciate assistance.

Find more posts tagged with

Accepted answers

Paul Newcome

You are correct. Another way of writing it would be...

=IF(AND([Director Approval]@row = "Approved", OR([Expected Budget]@row < 10000, [VP approval]@row = "Approved")), "Approved", "Declined")

To make sure that a "Submitted" entry overrides, you would want to place that first.

=IF(OR([Director Approval]@row = "Submitted", [VP approval]@row = "Submitted"), "Waiting for approval", original_formula)

All comments

Paul Newcome

Try something like this...

=IF([Director Approval]@row = "Approved", IF(OR([Expected Budget]@row < 10000, [VP approval]@row = "Approved"), "Approved", "Declined"), "Declined")

Vivien Chong

@Paul Newcome thanks for your prompt assistance. This formula works.

I am trying to understand how you derive this formula, am I right to think in the following flow:

1) Regardless of any amount of budget, Director will still need to approve first, otherwise it's decline.

2) Option 1 will be when budget below 10000 and director approved, then it's approved.

3) Option 2 will be when VP and Director approved, then it's approved.

4) Otherwise, it's declined.

I am wondering next time when I need to do formula again, how can I first think of the above flow 😅

By the way @Paul Newcome if I want to add a new condition: If either Director approval or VP approval shows "submitted", display "waiting for approval".

How should I add the above condition into the existing formula or the entire flow will change again?

Paul Newcome

You are correct. Another way of writing it would be...

=IF(AND([Director Approval]@row = "Approved", OR([Expected Budget]@row < 10000, [VP approval]@row = "Approved")), "Approved", "Declined")

To make sure that a "Submitted" entry overrides, you would want to place that first.

=IF(OR([Director Approval]@row = "Submitted", [VP approval]@row = "Submitted"), "Waiting for approval", original_formula)

Vivien Chong

Thanks @Paul Newcome .

It works and my problem is resolved.

Have a blessed day!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.