Harvey Ball Status between % numbers

Rick Girard

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!

Adrian Mandile CHESS

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

Rick Girard

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....

Adrian Mandile CHESS

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