Why am I getting an #INCORRECT ARGUMENT error when conditions meet the last IF/AND statement?
=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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!