Return a string value if cell is under, over, and between certain values (IF) function

Kayla Q

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

Paul Newcome

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

Paul Newcome

Percentages are stored on the back-end 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.

Kayla Q

Hi @Paul Newcome

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

Paul Newcome

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

Kayla Q

That worked!

It's amazing what a difference a single parenthesis can make 😊

Thanks!

Paul Newcome

Happy to help. 👍️

Kayla Q

@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:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. 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 :(

Paul Newcome

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

Kayla Q

@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!

Paul Newcome

@Kayla Q Happy to help. 👍️