Logic Needed for Turning on the Gray Stoplight

Todd Harrison

I am trying to add logic in the following string that will turn on the "Gray" stop light ball if the % Complete cell is left blank. 

=IF([% Complete]18 < 0.5, "Red", IF(AND([% Complete]18 >= 0.5, [% Complete]18 < 1), "Yellow", IF([% Complete]19= 1, "Green", IF([% Complete]19= blank, "Gray")))

I have tried "blank" and "null" and neither one works.  Any of you super users out there figured this out yet?  Thanks.

Todd Harrison

All of those should say [% Complete]18.  I know that is not the issue.  Thanks.

 

Mike Wilday

Try using isblank in the IF statement. 

https://help.smartsheet.com/function/isblank

=IF([% Complete]18 < 0.5, "Red", IF(AND([% Complete]18 >= 0.5, [% Complete]18 < 1), "Yellow", IF([% Complete]19= 1, "Green", IF(ISBLANK([% Complete]18), "Gray")))

Todd Harrison

Thanks, Mike.  That worked but only after I moved the Gray logic to the front of the logic string.  You rock!  

 

Mike Wilday

That is probably because one of your other flags are true and firing first. 

Mike Wilday

Glad I could help you out.