Nested OR statement to return a different value
I am trying to create a formula where it looks at 3 cells and returns a different value for the cell where the statement is true, but if multiple cells return true it also needs to return the last cell where that true value occurred:
=IF(OR([23-24 SY Visit Count]@row >= 1, 1, OR([22-23 SY Visit Count]@row >= 1, 2, OR([21-22 SY Visit Count]@row >= 1, 3, OR([20-21 SY Visit Count]@row >= 1, 4, OR([19-20 SY Visit Count]@row >= 1, 5))))))
keeps returning incorrect argument, so not sure why this is happening. I have some other columns that count if the previous 3 years and previous 5 years has a value.
But this formula needs to return a 1 if the 1st reference is 2 if the 2nd and 3 if it's in the 3rd. It also needs to return the last true value encountered, so if the 1st and the last have a value, then it should return the last true value.
Best Answer
-
Hey @Kevin7859
I think nested IF( ) statements here are going to be a better bet than using OR( )s. Since you already have the 'Visit Last 3 Years' as a helper column, you can use that as your base IF( ) condition, and then just sequentially look back at the rest of the SY columns individually to assign the 1, 2, or 3 result. Give this formula a shot:
=IF([Visit Last 3 Years]@row = 0, 0, IF([23-24 SY Visit Count]@row > 0, 1, IF([22-23 SY Visit Count]@row > 0, 2, IF([21-22 SY Visit Count]@row > 0, 3))))
It would be the same thing if you wanted to look back all five years - you'll just add the '[20-21 SY Visit Count]' and '[19-20 SY Visit Count]' as additional nested IF( ) conditions at the end of the above.
-MCS
Answers
-
Hey @Kevin7859
I think nested IF( ) statements here are going to be a better bet than using OR( )s. Since you already have the 'Visit Last 3 Years' as a helper column, you can use that as your base IF( ) condition, and then just sequentially look back at the rest of the SY columns individually to assign the 1, 2, or 3 result. Give this formula a shot:
=IF([Visit Last 3 Years]@row = 0, 0, IF([23-24 SY Visit Count]@row > 0, 1, IF([22-23 SY Visit Count]@row > 0, 2, IF([21-22 SY Visit Count]@row > 0, 3))))
It would be the same thing if you wanted to look back all five years - you'll just add the '[20-21 SY Visit Count]' and '[19-20 SY Visit Count]' as additional nested IF( ) conditions at the end of the above.
-MCS
-
That worked great, thank you.
I had thought I needed the nested OR in the statement.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!