Harvey Ball Status between % numbers
I want to use the Harvey Balls to flag a status as follows:
70% or less = RED
greater that 70% and less than 100% = YELLOW
100% = Green
what I've written:
=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row > 0.7, IF([% Complete]@row < 1, "Yellow", "Green"))))
I get INCORRECT ARUGMENT SET error on this. If i remove the AND and last IF:
=IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row > 0.7, "Yellow"))
this will work but it doesnt address the between 7099% (less than 100) condition
Having trouble incorporating that 'and' statement to get that middel "Yellow" condition.
thank you!
Best Answer

Hi Rick,
I'd be inclined to simplify it somewhat, without the need for the AND() function at all, by addressing the extremes of [% Complete] (i.e. when it = 1.0, or is < 0.7), then leaving the middle state as the last 'catchall' alternative. Try:
=IF([% Complete]@row = 1, "Green", IF([% Complete]@row < 0.7), "Red", "Yellow"))
Adrian Mandile
CHESS Consulting Australia  Smartsheet Solution Provider Gold Partner
Collaborative  Holistic  Effective  Systems  Solutions
Answers

Thanks i fixed it:
=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row > 0.7, [% Complete]@row < 1), "Yellow", "Green"))
I still have trouble knowing where to put the parens....

Hi Rick,
I'd be inclined to simplify it somewhat, without the need for the AND() function at all, by addressing the extremes of [% Complete] (i.e. when it = 1.0, or is < 0.7), then leaving the middle state as the last 'catchall' alternative. Try:
=IF([% Complete]@row = 1, "Green", IF([% Complete]@row < 0.7), "Red", "Yellow"))
Adrian Mandile
CHESS Consulting Australia  Smartsheet Solution Provider Gold Partner
Collaborative  Holistic  Effective  Systems  Solutions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!