Forum Discussion
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 explaining the data. Blank cells = no funding for that month.
The funding should be in consecutive months and if it is not it needs to be corrected / looked into.
So, I want to either use an If statement that says whether or not the cells are consecutive (no blanks between the 1st and last month funded) or a dynamic countblank function that would range from the first non-blank then the last non blank.
This is just an example, the data I'm using has hundreds of rows and would not be ideal to have to automatically but in the range.
Wondering if I could make it dynamic with a min/max function?
Please help
=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.
4 Replies
=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.
