Options
Susan Gyopar ✭✭
01/04/21 in Formulas and Functions
Hi, I've never used a combined IF with an AND function and I have to perform this function on a test (beginner level)
Use a nested-if formula to automate the RYG status balls in the "Status "column, depending on the "% Complete" for each request.
If a request is less than 70% complete, turn the "Status" column into a "Red" status ball
If a request is 70% or above and less than 100% complete, turn the "Status" column into a "Yellow" status ball
If a request is 100% complete, turn the "Status" column into a "Green" status ball
Do I put this formula in the status column that is already assigned as a symbol column with RYG balls - I think YES
There is a column named % Complete which is filled in with %s
My formula (error message = unparseable)
= IF([%complete]3<70%,”red”, IF(AND([% complete]3>=70%, [% complete]3<100%,)”yellow”,”green”))
What have I done wrong?
Thanks so much,
Susan
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
01/04/21 Answer ✓
Options
Hi @Susan Gyopar
There were multiple errors in your formula.
Try something like this.
=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row >= 0.7, [% Complete]@row < 1), "Yellow", "Green"))
Did that work/help?
I hope that helps!
Have a fantastic week & Happy Holidays!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
2 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
01/04/21 Answer ✓
Options
@Susan Gyopar
Excellent!
You're more than welcome!
The @row and @cell is a best practice.
@row can be used when you're referencing the same row and also removes the need to think about row numbers,
It takes fewer resources and also makes it possible to convert the formula to a Column Formula.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Answers
Andrée Starå ✭✭✭✭✭✭
01/04/21 Answer ✓
Options
Hi @Susan Gyopar
There were multiple errors in your formula.
Try something like this.
=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row >= 0.7, [% Complete]@row < 1), "Yellow", "Green"))
Did that work/help?
I hope that helps!
Have a fantastic week & Happy Holidays!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
2 · Share on FacebookShare on Twitter
Susan Gyopar ✭✭
01/04/21
Options
Thanks Andree, this formula worked! I understand that the % need to be expressed in decimals, noted. The course I took did not show me that I must use @row, instead the specific row was entered. Do I always use @row, or only if I will use this formula in a drag & fill along the full column?
Thanks again for correcting my errors
Susan
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
01/04/21 Answer ✓
Options
@Susan Gyopar
Excellent!
You're more than welcome!
The @row and @cell is a best practice.
@row can be used when you're referencing the same row and also removes the need to think about row numbers,
It takes fewer resources and also makes it possible to convert the formula to a Column Formula.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
TeamElevation ✭✭
11/04/22
Options
This formula worked for me:
=IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row < 1, "Yellow", "Green"))
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!
