Nested IF , AND formula
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
Question:
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
Best Answers
-
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.
-
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.
Answers
-
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.
-
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
-
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.
-
This formula worked for me:
=IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row < 1, "Yellow", "Green"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!