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