Options
Chris.fowler49286 ✭
05/29/18 edited 12/09/19 in Formulas and Functions
Hello Team,
This would be a great formula to incorporate into my daily SmartSheets processwithin my organization. I would like to know how to create a nested IF statementif this is the correct formulato use,that uses an RYG (red, yellow, green) light flag formula to set the status of a task based on its percentcomplete. I have not been able to figure this one out. If a task is less than 50% complete, turn the RYG light "Red" If a task is between 50-99% complete, turn the RYG light "Yellow" If a task is 100% complete, turn the RYG light "Green"
Thank you,
Tags:
- Education
- Formulas
- Project Management
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
05/29/18
Options
Try this formula. Replace "Percentage complete column name" with your own column name. And replace 23 througout the formula with whatever row you are referencing.
=IF([Percentage complete column name]23 < .5, "Red", IF(AND([Percentage complete column name]23 >= .5, [Percentage complete column name]23 < 1), IF([Percentage complete column name]23 = 1, "Green")))
0 · Share on FacebookShare on Twitter
Todd Harrison ✭
05/29/18
Options
Mike, I also had the same question and tried your formulawith no luck. I changed the % Completed to reference the appropriate cell but kept getting an "Unparsable" error.
0 · Share on FacebookShare on Twitter
Chris.fowler49286 ✭
05/29/18 edited 05/31/18
Options
Hello Mike,
What row and column should the formula be inserted to?
Thanks Mike
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
05/29/18
Options
Whoops I forgot the then response for the yellow section. Try this rewrite. Make sure to update the column name and the row number.
=IF([Percentage complete column name]23 < .5, "Red", IF(AND([Percentage complete column name]23 >= .5, [Percentage complete column name]23 < 1), "Yellow", IF([Percentage complete column name]23 = 1, "Green")))
0 · Share on FacebookShare on Twitter
Chris.fowler49286 ✭
05/29/18
Options
Hello Mike,
I cant get it to work, also which row and column should the formula be added.
Thanks,
0 · Share on FacebookShare on Twitter
Chris.fowler49286 ✭
05/29/18
Options
Hello Mike,
Got it and it works!
Thank you Sir,
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
05/29/18
Options
You are welcome! Glad I could be of help!
0 · Share on FacebookShare on Twitter
Todd Harrison ✭
05/29/18
Options
That worked for me as well. Thanks for the help and quick response.
0 · Share on FacebookShare on Twitter
Chris McKay ✭✭✭✭✭✭
05/29/18
Options
I'm amazed that this is not built into Smartsheet. If it's not included in the Project Settings as a configuration item, at least create a template for these poor users. Seriously.... what other question is asked more on these Community forums other than "Automate RYG"?
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
05/29/18
Options
I agree. The issue of percentages displaying as decimals is very hidden and not intuitive. If not for greats like J. Craig Willams I would not have know this information.
0 · Share on FacebookShare on Twitter
carsonc ✭
09/30/19
Options
Was able to tailor this to my sheet as well - thank you!
=IF([% Complete]23 < .5, "Red", IF(AND([% Complete]23 >= .5, [% Complete]23 < 1), "Yellow", IF([% Complete]23 = 1, "Green")))
Please note that the #s next to [% Complete] reflect the row #.
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
- 11 Community Corner Newsletter
- 69 Brandfolder
- 120 Just for fun
- 41 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 244 Events
- 8 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
