Nested OR statement to return a different value

Kevin7859

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.

Mark Safran

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