# Nested IF Formula with Blank Cell Condition

Options
edited 12/09/19

Hi,

I was able to get this formula to work, where:

• 90% or higher = Green RYGG ball
• 76% to 89% = Yellow RYGG ball
• 75% or lower = Red RYGG ball

=IF([Proj. Blended Average]4 >= 0.9, "Green", IF([Proj. Blended Average]4 >= 0.76, "Yellow", IF([Proj. Blended Average]4 <= 0.75, "Red")))

However, I am struggling to find a solution where a blank cell under the [Proj. Blended Average] column results in a "Gray" ball (or no ball would also be fine). I've tried a couple variations of the formula below to no avail (resulting in either a red ball or the "#UNPARSEABLE" error).

=IF([Proj. Blended Average]4 >= 0.9, "Green", IF([Proj. Blended Average]4 >= 0.76, "Yellow", IF([Proj. Blended Average]4 <= 0.75, "Red", IF(ISBLANK([Proj. Blended Average]4, "Gray")))))

Additionally, replacing any colors in the first formula with "Gray" does result in a gray ball. The attached screenshot shows the above formula under January Targets (Column 10) and the below formula under February Targets (Column 10).

Does anyone have any insight where I am taking a wrong turn?

Tags:

• ✭✭✭✭✭✭
Options

This should work.

=IF([Proj. Blended Average]4 >= 0.9, "Green", IF([Proj. Blended Average]4 >= 0.76, "Yellow", IF([Proj. Blended Average]4 <= 0.75, "Red", IF(ISBLANK([Proj. Blended Average]5), "Gray"))))

You just had to put a closing parenthesis after ISBLANK formula there.

• Options

Thanks Mike! I still had trouble with the ISBLANK formula, but had success with:

=IF([Proj. Blended Average]4 = "", "Gray", IF([Proj. Blended Average]4 >= 0.9, "Green", IF([Proj. Blended Average]4 >= 0.76, "Yellow", IF([Proj. Blended Average]4 <= 0.75, "Red"))))

• ✭✭✭✭✭✭
Options

Awesome. Glad I was able to help point you in the right direction.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!