Options
Caitlin Gaines ✭✭
10/18/19 edited 12/09/19 in Formulas and Functions
I am trying to identify which projects need committee review vs those that canbe 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"))))))
Tags:
- Formulas
0 · Share on FacebookShare on Twitter
L_123 ✭✭✭✭✭✭
10/18/19
Options
=IF(AND([Currently Underway]@row = 1, IF(Timeline@row = "Yes", 1, 0) + IF([Count of Selections]@row > 1, 1, 0) + IF([Projected Capital]@row > 500000, 1, 0) + IF([Fast Tracked]@row = "Standard", 1, 0) >= 2), 1, 0)
Give that one a try and tell me if it works for you.
0 · Share on FacebookShare on Twitter
L_123 ✭✭✭✭✭✭
10/18/19
Options
Thought I should type this out to explain it, as it is usually a pain to read through if statement, especially nested ones with AND statements.
Start the IF Statement
=IF(
Start AND Statement. This AND only has 2 criteria
AND(
First criteria is that the currently underway has to be 1.
[Currently Underway]@row = 1,
The second and last criteria for the and statement is a group of if statements for your different criteria. Each returns a 1 if the criteria is met, and a 0 if the criteria is not met, then they are all added together
IF(Timeline@row= "Yes", 1, 0) +
IF([Count of Selections]@row > 1, 1, 0) +
IF([Projected Capital]@row > 500000, 1, 0) +
IF([Fast Tracked]@row = "Standard", 1, 0)
If the number of if statements above is greater than or equal to 2, then return true. else return false
>= 2), 1, 0)
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
10/18/19
Options
The only change I would make would be the first AND criteria
[Currently Underway]@row = 0
.
OP listed criteria as being NOT underway.
The thought process behind it though using the IF inside of the AND to create a running count of true's...This is actually rather brilliant! Nicely done!
0 · Share on FacebookShare on Twitter
L_123 ✭✭✭✭✭✭
10/18/19
Options
Ah I misread that. Thanks
0 · Share on FacebookShare on Twitter
L_123 ✭✭✭✭✭✭
10/18/19
Options
This solution is actually a boiled down version of some really complicated weighted mean formulas I've done for some finance type sheets. The concept is the still the same though. My favorite part about this is how easy they are to edit when you build them this way. It isn't a problem to add, delete, or edit criteria as every single one of them is only in one location in the formula.
0 · Share on FacebookShare on Twitter
Caitlin Gaines ✭✭
10/18/19
Options
You all are AMAZING! I spent way more time on this than necessary and actually was rather close yesterday, but missed the >=2!
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.1K Get Help
- 86 Global Discussions
- 88 Industry Talk
- 398 Announcements
- 12 Community Corner Newsletter
- 69 Brandfolder
- 121 Just for fun
- 42 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 251 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
