Using IF - AND to return color symbol based on data range

Options

Hello -

I'm having trouble with a formula and need some help. I want a cell to return a symbol for "Position Health" that is either Red, Green, or Yellow, with each being based on a specific data range that is represented in a different cell [Days Open]. For this range, Green would equate to </= 60 days, Yellow would be 61-90 days, and Red is greater than 90 days.

Here is the formula I'm using: =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", IF(AND([Days Open]@row > 90, "Red"))))

This is giving me an "Incorrect Argument Set" return. Any help would be greatly appreciated.

Best Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @SNolan16

    In your last argument set for RED you still have the AND command you do not need.

    Try this...

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", IF([Days Open]@row > 90, "Red")))

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @SNolan16 ,

    It's most likely the last "IF" statement causing issues, you do not need an "AND" since you are only looking at 1 criteria.

    Change it to the following.

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", IF([Days Open]@row > 90, "Red")))

    Alternatively, you can remove the last "IF altogether since it would be anything that wasn't already caught.

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", "Red"))

    Hope this helps,

    Dave

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @SNolan16

    In your last argument set for RED you still have the AND command you do not need.

    Try this...

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", IF([Days Open]@row > 90, "Red")))

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @SNolan16 ,

    It's most likely the last "IF" statement causing issues, you do not need an "AND" since you are only looking at 1 criteria.

    Change it to the following.

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", IF([Days Open]@row > 90, "Red")))

    Alternatively, you can remove the last "IF altogether since it would be anything that wasn't already caught.

    =IF([Days Open]@row <= 60, "Green", IF(AND([Days Open]@row > 60, [Days Open]@row <= 90), "Yellow", "Red"))

    Hope this helps,

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!