Nested IF function using negative percent values & RYG balls

Carly Boulton

 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.

SoS | Dan Palenchar

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"))))

David Joyeuse

Hi @Carly Boulton

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.

Carly Boulton

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...?

David Joyeuse

@Carly Boulton

#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...)

Andrée Starå

Hi @Carly Boulton

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!