Nested IF formula to return a symbol based on a range
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.
Answers

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
Connor Hartford

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

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

@DEBRA LOPEZ Aren't we all? Hahah
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!