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 70-99% (less than 100) condition

Having trouble incorporating that 'and' statement to get that middel "Yellow" condition.

thank you!

• ✭✭✭✭✭

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 'catch-all' alternative. Try:

=IF([% Complete]@row = 1, "Green", IF([% Complete]@row < 0.7), "Red", "Yellow"))

CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions

• ✭✭✭✭✭

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 'catch-all' alternative. Try:

=IF([% Complete]@row = 1, "Green", IF([% Complete]@row < 0.7), "Red", "Yellow"))