Nested IF function using negative percent values & RYG balls
I'm using a nested IF statement to determine the colour of RYG balls in Column 'YTD Health', using data from an adjacent column of percentages. The percentages listed are a combination of negative and positive values.
What I want to happen is this:
Values 0 to -40% = Yellow
Values less than -41% = Red
Values 0 and greater = Green
Anything else = Error
I know that because I'm referencing percentages that I need to use decimals, so my current IF statement in the 'YTD Health' column looks like this:
=IF([YTD Position (Variance from target)]1 <= -0.4, "Yellow",
IF([YTD Position (Variance from target)]1 <= -0.41, "Red",
IF([YTD Position (Variance from target)]1 >= 0, "Green", "Error")))
It's working for most cells, but not all. E.g. -80% should be red...? See attached.
Answers
-
With nested IF statements the first true statement met ends the logic chain, since -80% is less than -0.4., it will go yellow. You need to reorder to get the Red criteria first, and modify the condition for yellow to have it be less than 0. You also need to pick if you want Yellow or Green for values of 0, as written 0 will be yellow but you can change this by going from <=0 to just <0.
=IF([YTD Position (variance from target)]@row <= -0.41, "Red", IF([YTD Position (variance from target)]@row <= 0, "Yellow", IF([YTD Position (variance from target)]@row >= 0, "Green", "Error")))
Also, note that you will never get an error this way, at least based on my tests.
If you want to check for Errors, which I assume in this case are non-numerical values, nest the IF statement within ISNUMBER as follows
=IF(ISNUMBER([YTD Position (variance from target)]@row), IF([YTD Position (variance from target)]@row <= -0.41, "Red", IF([YTD Position (variance from target)]@row <= 0, "Yellow", IF([YTD Position (variance from target)]@row >= 0, "Green", "Error"))))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Your first IF statement is wrong, according to what you said you said <=-0.4. While your condition is between 0 and -0.4.
I'd suggest you to reverse your formula to make it easier to read:
=IFERROR(IF([YTD Position (Variance from target)]1 >= 0, "Green", IF([YTD Position (Variance from target)]1 <= -0.41, "Red", "Yellow")), "Error")
Yellow will be your default value if it's not green or red. I've inserted the "Error" condition you returned within an IFERROR function, but I'm not sure that suits what you want to do here.
Hope it helped.
-
Thank you both. I have taken your advice and the colour balls are now correct - thank you so much! I am still having issues with displaying an Error message though. I have tried both suggestions above (ISNUMBER and IFERROR), but can't seem to get a result, only '#UNPARSEABLE'.
Dan, Which example is best to take from above as the statement in your text differs from the screenshot...?
-
#UNPARSEABLE means Smartsheet cannot process your formula, usually that means there's a typo somewhere, or a parenthesis at the wrong place... a semi colon instead of a coma (or vice versa...)
-
Can you maybe share the formula that isn't working? That would make it easier to help.
I hope that helps!
Be safe and have a fantastic weekend!
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!