Nested IF formula to return a symbol based on a range

Laura Briggs

Hi,

I am trying to write a formula that will look at the value in a cell and return a RYG dependant on the value being either below the value in another cell, within a mid range or above the range. I think the issue I am having is in relation to the range section, do i need to enter the range value into the formula rather than looking at another cell?

Any advice would be appreciated?

Thank you.

Connor Hartford

Hi Laura,

Here's an example of a Green, Yellow, Red formula.

Green will show up for 0 and above, Yellow for below 0 and above -10, and Red for -10 or below.

=IF([Column 1]1 >= 0, "Green", IF(AND([Column 1]1 < 0, [Column 1]1 > -10), "Yellow", IF([Column 1]1 <= -10, "Red", "")))

I hope this helps,

Connor

Paul Newcome

@Laura Briggs The most direct answer to your question would actually be more along the lines of a "Yes and no". The cell reference IS your "range".

@Connor Hartford's solution is a perfect example of this.

I personally would make a few tweaks though to help increase flexibility, durability, and efficiency.

First, if the formula is looking at cells on the same row as the formula itself, I suggest using @row references instead of specifying a row number. It is a little bit easier for the sheet to run and you don't have to worry about anything getting messed up by a typo or dragfill.

[Column Name]@row

Additionally, we can leverage some logic built into nested IF statements to shorten it up a little bit. Nested IF's work from left to right and will output the first true value it runs into. SO if it makes it to the second IF, bu default the first IF must be false. This means you don't have to specify it using an AND statement in your second IF.

So where Connor has

=IF([Column 1]1 >= 0, "Green", IF(AND([Column 1]1 < 0, [Column 1]1 > -10), "Yellow", ..............................................

You have already specified what to do in the first IF if the number is greater than zero. That means to get to the second IF, the number MUST be less than zero, so we don't really need to specify that for the formula.

=IF([Column 1]1 >= 0, "Green", IF([Column 1]1 > -10, "Yellow", ..............................................

Finally, if you only have 3 different scenarios and want to populate a color for all rows, you don't have to enter any criteria for the third output. You have already specified what to do for greater than zero and greater than negative ten. Every other number by default must be less than negative ten, so you can just tell the formula to populate "Red" for any number that does not meet the criteria of the two IF's already established.

=IF([Column 1]1 >= 0, "Green", IF([Column 1]1 > -10, "Yellow", "Red")

Debra Nicole Lopez

Thanks...I was one key stroke from losing my mind

Paul Newcome

@DEBRA LOPEZ Aren't we all? Hahah