Forum Discussion
Solved
Determine if cells are consecutive/find blanks in a dynamic range
I have a large set of data that shows months in the top column, then amounts in the rows below - indicating funding within a certain month. the months aren't really important for this question, just ...
=IF(COUNTBLANK(INDEX(A2:L2,SMALL(IF(A2:L2<>"",COLUMN(A1:L1)),1)):INDEX(A2:L2,LARGE(IF(A2:L2<>"",COLUMN(A1:L1)),1))),"non-consecutive","consecutive")
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=IF(COUNTBLANK(INDEX(A2:L2,SMALL(IF(A2:L2<>"",COLUMN(A1:L1)),1)):INDEX(A2:L2,LARGE(IF(A2:L2<>"",COLUMN(A1:L1)),1))),"non-consecutive","consecutive")
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
