IF with multiple contingencies

Options

=IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row > 5, "yes", IF(AND([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row = 5, "hold", IF(AND([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row < 5, "no")))))

this formula is #unparseable

can anyone give me an idea where to start for a fix?

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 09/21/22 Answer ✓
    Options

    Hey @Dell55

    An IF/AND combination is used only when you have a simultaneous condition that must be met. For example, IF(AND(Activity@row="Walk Outside", Weather@row="Rainy"), "Bring an Umbrella"). You do not have any listed simultaneous conditions and the syntax for an IF/AND was incorrect as the AND was not closed off.

    =IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row > 5, "yes", IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row = 5, "hold", IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row < 5, "no")))

    The above formula will only work if the responses in your columns are numbers values (a checkbox will not count). If you get further errors in your formula, please provide a screenshot of your columns with data so the community can determine what type of data you are dealing with and how your columns are arranged.

    Does the above formula work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 09/21/22 Answer ✓
    Options

    Hey @Dell55

    An IF/AND combination is used only when you have a simultaneous condition that must be met. For example, IF(AND(Activity@row="Walk Outside", Weather@row="Rainy"), "Bring an Umbrella"). You do not have any listed simultaneous conditions and the syntax for an IF/AND was incorrect as the AND was not closed off.

    =IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row > 5, "yes", IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row = 5, "hold", IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row < 5, "no")))

    The above formula will only work if the responses in your columns are numbers values (a checkbox will not count). If you get further errors in your formula, please provide a screenshot of your columns with data so the community can determine what type of data you are dealing with and how your columns are arranged.

    Does the above formula work for you?

    Kelly

  • Dell55
    Dell55 ✭✭✭✭
    Options

    Oh thanks for the info @Kelly Moore,

    well said, i thought the IF(AND was necessary because of the 3 different designated out comes. I see now.

    I'll try it out and get back to you

  • Dell55
    Dell55 ✭✭✭✭
    Options

    worked out great with the formula you gave

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!