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.
Best Answer
-
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)
Answers
-
Try something like this...
=IF([Director Approval]@row = "Approved", IF(OR([Expected Budget]@row < 10000, [VP approval]@row = "Approved"), "Approved", "Declined"), "Declined")
-
@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?
-
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)
-
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!