Why am I getting an #INCORRECT ARGUMENT error when conditions meet the last IF/AND statement?

Abby P
Abby P ✭✭
edited 04/15/24 in Formulas and Functions

=IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, IF(AND([Deadline for implementing this project]@row - [Submission Date]@row <= 60, [Deadline for implementing this project]@row - [Submission Date]@row >= 30, 2))))


I get the error for when rows' [Deadline for implementing this project] - [Submission Date] is greater than 30 but less than 60.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    It looks like the closing parenthesis for the AND is in the wrong place:

    Instead of

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, IF(AND([Deadline for implementing this project]@row - [Submission Date]@row <= 60, [Deadline for implementing this project]@row - [Submission Date]@row >= 30, 2))))

    Try

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, IF(AND([Deadline for implementing this project]@row - [Submission Date]@row <= 60, [Deadline for implementing this project]@row - [Submission Date]@row >= 30), 2)))

    As the IFs are nested in the value_if_false position they are only evaluated if the previous one is false. Your first IF returns 3 for gaps* less than 30. The second is evaluated only if the gap is 30 or more. If turns 1 is the gap is more than 60. So the third IF is only evaluated if the gap is 30 or more and less than 60. So you don't really need the third IF or the AND. This would do the same

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, 2))

    *I am referring to days between "Deadline for implementing the project" and "Submission Date" as a "gap" as it is a lot shorter!

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    It looks like the closing parenthesis for the AND is in the wrong place:

    Instead of

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, IF(AND([Deadline for implementing this project]@row - [Submission Date]@row <= 60, [Deadline for implementing this project]@row - [Submission Date]@row >= 30, 2))))

    Try

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, IF(AND([Deadline for implementing this project]@row - [Submission Date]@row <= 60, [Deadline for implementing this project]@row - [Submission Date]@row >= 30), 2)))

    As the IFs are nested in the value_if_false position they are only evaluated if the previous one is false. Your first IF returns 3 for gaps* less than 30. The second is evaluated only if the gap is 30 or more. If turns 1 is the gap is more than 60. So the third IF is only evaluated if the gap is 30 or more and less than 60. So you don't really need the third IF or the AND. This would do the same

    =IF([Deadline for implementing this project]@row - [Submission Date]@row < 30, 3, IF([Deadline for implementing this project]@row - [Submission Date]@row > 60, 1, 2))

    *I am referring to days between "Deadline for implementing the project" and "Submission Date" as a "gap" as it is a lot shorter!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!