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
-
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"))))
Answers
-
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")))))
-
Thank you, but unfortunately, it still errors out as #UNPARSEABLE.
-
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.
-
Yes...I removed the extra paren.
-
Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?
-
See above
-
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"))))
-
OMG!!! I see it now. Thank you. It is working now.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!