Issues combining 2 formulas

Vivien Chong

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.

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)

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. 👍️