pixel_ ✭✭
edited 12/09/19 in Formulas and Functions


I have a formula for a Health/RYG column:

=IF([Working Days Remaining]32 < 0, "Red", IF([Working Days Remaining]32 < 2, "Yellow", "Green"))

It works well except if Working Days Remaining is blank, I would like the cell to stay blank. I think this is where ISBLANK comes in but I am struggling to use it. Can I get some help with this?



Many thx!



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try this:

    =IF(Isblank([Working Days Remaining]@row), "", IF([Working Days Remaining]@row< 0, "Red", IF([Working Days Remaining]@row< 2, "Yellow", "Green")))

    I switched the indicated from 32 to @row so you can copy and paste the formula into any row and ensure that the formula will work with the current row's data. 

  • pixel_
    pixel_ ✭✭

    Eureka! It worked.

    I was missing the first ) after the ISBLANK function. Thanks, Mike!


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You're welcome. I'm glad I could help you get it working. Yes. Every formula you open in your IF statements needs to be closed before you continue your IF statements except nested if Statements themselves. You can kind of see this in the helper dropdown of the formula as you're typing it. See my screenshot as I type so you can see where you are in the current formula. 


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    An additional note to Mike's tip on closing out statements...


    I build out each statement individually and use cell references. It helps with trouble shooting and making sure you have everything you need. I then replace the cell references with the formulas that are in those particular cells, and it's done.


    A short example using an INDEX/MATCH........


    =INDEX(range_to_pull_data_from, row_number, [column_number])

    =MATCH(search_value, search_range, match_type)


    I want to use the MATCH function to populate a row number for my INDEX function.


    So I will use the cell [Column1]1 for my INDEX and put my MATCH in [Column1]2.


    In [Column1]1 I would have:


    =INDEX([Column Name]1:[Column Name]30, [Column1]2)


    Then in [Column1]2, I would build out my MATCH statement.


    Once I have both working individually, I just copy/paste the MATCH formula from [Column1]2 into the INDEX formula where is says "[Column1]2".


    This ensures that both functions are working as needed, and my MATCH statement is properly closed.


    It may not seem like much in this example, but some of the crazier messes where you have nested SUBSTITUTES using @cell references within a COUNTIFS statement that is part of a nested IF statement or some crazy mess like that... It definitely makes life much easier.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Sometimes formulas are giant mazes. 

    If only there was a hedgetrimmer. #bartSimpson

    maze - alice in wonderland .gif

    Maze Shortcut - simpsons.gif

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!