Health Field Formula Issue

krinnap

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.

Paul Newcome

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

Paul Newcome

Try removing one of the closing parenthesis from the end of the formula.

You should have 4, but it looks like you have 5.

=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")))))

krinnap

Thank you, but unfortunately, it still errors out as #UNPARSEABLE.

Paul Newcome

That was after ensuring you only had 4 closing parenthesis on the end? I left all 5 in my post so that you could see the discrepancy and how I was figuring you only needed 4.

krinnap

Yes...I removed the extra paren.

Paul Newcome

Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?

krinnap

krinnap

See above

Paul Newcome

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

krinnap

OMG!!! I see it now. Thank you. It is working now.

Paul Newcome

Happy to help. 👍️