I am trying to identify which projects need committee review vs those that can be auto-approved.
I have 5 columns with the following selections:
- Currently Underway? - Checked/Unchecked (I want values that are unchecked/=0)
- Helper - Timeline - Returns Yes/No based on formula. (I want values that = Yes")
- Helper - Count of Selections - Formula that returns number of selections made in another cell. (I want selections >1)
- Projected Capital/Expense Costs - Text/Number Field (I am looking for values > 500,000)
- Helper - Fast Tracked - Formula that returns "Fast Tracked" or "Standard". (I want those that are standard to require committee approval)
**The trick is that the project must not be underway and at least 2 of the other columns must meet the criteria. I've created about 100 formulas already and most return incorrect argument/unparsable. The below formula runs, but isn't returning the correct value.
=IF(AND([Helper - Timeline]@row = "Yes", [Helper - Count of Selections]@row > 1), "Requires Committee Review", IF(AND([Helper - Timeline]@row = "Yes", [Projected Capital/Expense Costs]@row > "500,000"), "Requires Committee Review", IF(AND([Helper - Timeline]@row = "Yes", [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", IF(AND([Helper - Count of Selections]@row > 1, [Projected Capital/Expense Costs]@row > "500,000"), "Requires Committee Review", IF(AND([Helper - Count of Selections]@row > 1, [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", IF(AND([Projected Capital/Expense Costs]@row > "500,000", [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", "Auto-Approved"))))))