Formula Question - Nested IF
Objective: "Status" column with symbols should update accordingly as per the information in the "% Complete" column which is in percent format
Conditions: If "% Complete" is 100% then "Status" column should show green symbol, If above 70% & less than 100% it should show yellow symbol else red symbol.
Need help with below questions
1.) What is wrong with this formula? It is showing as unparsable
=IF([% Complete]@row = 1, "Green", IF ([% Complete]@row > 0.7, "Yellow", "Red"))
2.) While writing the above formula i observed another issue which is for the formula to work i was supposed to write '1' instead of '100%'. Any idea why system is behaving such way?.
Example: =IF([% Complete]@row = 1, "Green") --> works
but =IF([% Complete]@row = 100%, "Green") --> doesn't work
Answers
-
Hi @Mangesh W
I hope you're well and safe!
Try something like this.
=IF([% Complete]@row = 1, "Green", IF([% Complete]@row > 0.7, "Yellow", "Red"))
Did that work/help?
Regarding the percentage
Smartsheet looks at the numbers in a column formatted for percentages as values between 0 and 1. You'd need to use decimal values instead for it to work
25% = 0,25 (0.25)
50% = 0,5 (0.5)
100% = 1
Depending on your country/region, you'll need to exchange the comma for a period.
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!