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

Kayla Q
Kayla Q ✭✭✭✭✭
edited 06/23/22 in Formulas and Functions

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!