Forum Discussion
Determine if cells are consecutive/find blanks in a dynamic range
=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.
I just tried this on my actual data, which has additional columns of data before the months begin. I'm getting the error "Formula Omits Adjacent Cells". I don't want to include those cells and a good majority of the cells also have a reference error. If I delete the columns the formula works, but need the additional data on the sheet. Also, some are showing non-consecutive even though they are consecutive
Any thoughts on how to fix this error?
Also, do I need to lock in the date cells when dragging it down?
In your actual dataset the months are in columns D to O. Therefore the formula has to be adapted to the actual dataset. In your original question the months are in columns A to L and i suggested a formula for this data layout in my first reply.
=IF(COUNTBLANK(INDEX(D2:O2,SMALL(IF(D2:O2<>"",COLUMN(D1:O1)-3),1)):INDEX(D2:O2,LARGE(IF(D2:O2<>"",COLUMN(D1:O1)-3),1))),"non-consecutive","consecutive")
You don't need to lock in the date cells when dragging down the formula. If you don't work with Office 365 or Excel for the web or Excel 2021 you have to enter the formula as an arrayformula with ctrl+shift+enter.