Formula not working...need to eliminate N/A then determine Health bubble.

Options

Hi All,

This formula is not working. The goal is to eliminate the N/A lines based on Status and then assign Health color based on looking at %Complete.

=IF((Status@row = "N/A", "", [% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red"))))))

Any de-bug help would be greatly appreciated!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @sgodown106626

    It looks like you're just missing one "IF(" in front of your second statement!

    =IF(Status@row = "N/A", "", IF([% Complete]@row = 1,

    Try this:

    =IF(Status@row = "N/A", "", IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red"))))))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!