Return a string value if cell is under, over, and between certain values (IF) function
I'm trying to get an IF function to work in the Health column of my sheet using the following logic:
If budget used cell is less than or equal to 74, return "GREEN"
If budget used cell is between 75 and 100, return "ORANGE"
If budget used cell is greater than or equal to 100, return "RED"
I am using the below function:
=IF([Budget Used]@row <= 74, "GREEN", IF(AND([Budget Used]@row >= 75, [Budget Used]@row <= 199, "ORANGE", IF([Budget Used]@row >= 100, "RED"))))
Every cell is returning "GREEN," which isn't right. As you can see from the screenshot below, row 3 is at 164% and should be returning "RED." I've spent about 2 hours trying different variations of this function, but nothing seems to work.
Can someone help me figure this out?
SCREENSHOT
Best Answer

@Kayla Q Try moving one of the closing parenthesis from the very end of the formula to after the .99 to close out the AND statement before moving on to the IF statement outputting "RED".
Answers

Percentages are stored on the backend as "a part of a whole"/decimal.
100% = 1.00
75% = 0.75
50% = 0.50
so on and so forth.
Try changing the numbers in your formula to .74, 1.99, etc. and see if that helps.
I also want to point out that you have a little bit of overlapping logic.
Orange = less than or equal to 199
Red = greater than or equal to 100
This means that 150 would be flagged as "ORANGE" and not "RED" even though it is greater than 100 because nested IF statements start on the left and work towards the right and stop on the first true value.

Thanks for your help!
I made the change to account for the percentages. Also, the "199 was a typo" and should read "99."
Based on your feedback, I have changed the function to:
=IF([Budget Used]@row <= 0.74, "GREEN", IF(AND([Budget Used]@row >= 0.75, [Budget Used]@row <= 0.99, "ORANGE", IF([Budget Used]@row >= 1, "RED"))))
I think I'm missing another "AND" statement somewhere, as projects greater than 74 are returning "#INCORRECT ARGUMENT."

@Kayla Q Try moving one of the closing parenthesis from the very end of the formula to after the .99 to close out the AND statement before moving on to the IF statement outputting "RED".

That worked!
It's amazing what a difference a single parenthesis can make 😊
Thanks!

Happy to help. 👍️

@Paul Newcome I don't know why I struggle wo much with these IF/AND statements with multiple arguments. I'm stuck on this new use case.
The following arguments apply to the "Hours Worked  Life of Matter (auto fill) column
If less than 10, return "1"
If equal to or greater than 10 and less than 15, then "2"
If equal to or greater than 15, then "3"
I'm using the following:
=IF([Hours Worked  Life of Matter (auto fill)]@row < 10, "1", IF(AND([Hours Worked  Life of Matter (auto fill)]@row >= 10), [Hours Worked  Life of Matter (auto fill)]@row < 15, "2", IF([Hours Worked  Life of Matter (auto fill)]@row >= 15, "3", "")))
Any number in my reference column that is greater than 10 is returning #INCORRECT ARGUMENT SET
Cause
This error is presented under the following circumstances:
 For functions that take two ranges: The range sizes don’t match for the function.
 The function is missing an argument.
 There is an extra function in the argument.
Resolution
Correct the range size or arguments, adding or removing arguments in the formula.
I've been looking at this for quite a long time, and I can't figure out where I've gone wrong :(

@Kayla Q That is because you have your closing parenthesis misplaced for the AND function. Really though we don't even need the AND function because of how nested IFs work.
The read from left to right and stop on the first true value. This means that if it gets to the second IF, the first MUST be false. Therefore we do not have to specify it being false in the second IF.
To translate this into your above example... The first IF is what happens when the number is less than 10. By default, for the formula to move past this then the number MUST be greater than or equal to 10 for any following IFs. If it wasn't then it would have stopped there anyway.
Here is your formula with technically correct syntax:
=IF([Hours Worked  Life of Matter (auto fill)]@row < 10, "1", IF(AND([Hours Worked  Life of Matter (auto fill)]@row >= 10, [Hours Worked  Life of Matter (auto fill)]@row < 15), "2", IF([Hours Worked  Life of Matter (auto fill)]@row >= 15, "3", "")))
Here is my suggestion:
=IF([Hours Worked  Life of Matter (auto fill)]@row < 10, 1, IF([Hours Worked  Life of Matter (auto fill)]@row < 15, 2, 3))
Note: I also removed the quotes from around the numbers. If you are planning on using them as numbers in future calculations, leaving quotes around them outputs a text value that just looks like a number. This means you won't be able to sum/average/etc..
I also notice that the zero cells are left justified. If you have left the formatting of those cells alone, then they are not being registered as numbers which could lead to issues based on the above note.

@Paul Newcome I don't know what I would do without you!
Thanks for taking the time to explain the function in detail. Your suggestion works :)
I didn't know the information about the quotes  that's super helpful!
As for the column containing the zero cells, that's a completely different story. That column is using a VLOOKUP to pull information from another cell. If it doesn't find a match, it returns #N/A, so I wrote the function to replace #N/A with "0." The source data may or may not return data in the future (I'm using Data Shuttle to pull in outside data). With my current use case, i think this is fine. I right adjusted the column just to make this aesthetically pleasing. Thanks for pointing this out!

@Kayla Q Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!