Nested IF Formula with Blank Cell Condition

kglessner

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?

Thank you for your time.

Mike Wilday

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. 

kglessner

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

Mike Wilday

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