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 nonnumerical 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"))))
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!

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
Check out the Formula Handbook template!