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!

Best Answer

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    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 'catch-all' alternative. Try:

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

Answers

  • Rick Girard
    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
    Adrian Mandile CHESS ✭✭✭✭✭
    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 'catch-all' alternative. Try:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!