Issue with Nested IF Formula

Heather K

I am having trouble getting my nested if formula to work. I have the following formula:

=IF([DCO Status]@row = "Implemented", "Blue", IF(AND([Days Open]@row > 46, [Days Open]@row < 100), "Yellow", IF([Days Open]@row >= 100, "Red", IF([Days Open]@row <= 45, "Green", ""))))

It is working for all the cells except the blank ones are showing “Green” instead of a blank. Can somebody please help me?

Zachary Taylor

Hey Heather! 

I added an AND statement to specify that the [Days Open] cell should be below 45 and not blank to show green. This eliminates Smartsheet counting those blank cells in your criteria.

See if this works like you're expecting: 

=IF([DCO Status]@row = "Implemented", "Blue", IF(AND([Days Open]@row >= 46, [Days Open]@row < 100), "Yellow", IF([Days Open]@row >= 100, "Red", IF(AND([Days Open]@row <= 45, [Days Open]@row <> ""), "Green", ""))))

Hope this helps!!

Paul Newcome

Give this a try...

 

=IF([DCO Status]@row = "Implemented", "Blue", IF(ISNUMBER([Days Open]@row), IF([Days Open]@row >= 100, "Red", IF([Days Open]@row >= 46, "Yellow", "Green")), ""))

Heather K

Thank you. I stared at the formula forever before I found my mistake!

Paul Newcome

Happy to help.