Health Field Formula Issue

I'm working on a formula to set the Health status of each task line and continue to get #UNPARSEABLE errors.

The current formula:

=IF(Status@row = "Complete", "Green", IF(Status@row = "In Queue", "Blue", IF(OR([% Comparison]@row <-0.249, [End Date]@row > TODAY()), "Red", IF(AND([% Comparison]@row >=-0.249, [% Comparison] <=-0.15), "Yellow", "Green")))))

The data line I'm testing, for which I expect to see a health status of Yellow:

I'm not sure if it's a logical expression issue or something more basic. All feedback is appreciated.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Sorry about that. Not sure how I missed it the first time. You are missing a row reference on the second % cell reference.

    =IF(Status@row = "Complete", "Green", IF(Status@row = "In Queue", "Blue", IF(OR([% Comparison]@row <-0.249, [End Date]@row > TODAY()), "Red", IF(AND([% Comparison]@row >=-0.249, [% Comparison]@row <=-0.15), "Yellow", "Green"))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!