Nested IF Formula with Blank Cell Condition

Options
kglessner
kglessner
edited 12/09/19 in Formulas and Functions

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.

Screen Shot 2019-02-13 at 5.48.39 PM.png

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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. 

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

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