Find date of last consecutive 0

[Dempix]

Hello,

Trying to find a formula which will find/reference the date when the 12 (twelve) consecutive 0 (zeros) end.

I have numerous rows with numbers distributed by month. Some cells contain >0 zero values. Some 0 values.
I need to identify whether there are twelve consecutive zeros (I already have formula for that -> in picture, if =1, then there are 12 consecutive zeros, if =0, there are no 12 consecutive zeros) and then, identify the month of the last consecutive zero.

Currently used formula : =(SUM(IF(FREQUENCY(IF(D2:AL2<>"";IF(D2:AL2=0;COLUMN(D2:AL2)));IF((D2:AL2="")+(D2:AL2<>0);COLUMN(D2:AL2)))>=12;1))>0)+0

Maybe someone can help? Thanks

 

[Dempix]

Sure:

=AGGREGATE(14,6,O$1:AL$1/(COUNTIF(OFFSET(D2:O2,0,COLUMN(O2:AL2)-COLUMN(O2)),0)=12)/SIGN((P2:AM2<>0)+(COLUMN(O2:AL2)=COLUMN(AL2))),1)

The part in red is the list of dates. The assumption is that the dates are in ascending order.

=AGGREGATE(14,6,O$1:AL$1/(COUNTIF(OFFSET(D2:O2,0,COLUMN(O2:AL2)-COLUMN(O2)),0)=12)/SIGN((P2:AM2<>0)+(COLUMN(O2:AL2)=COLUMN(AL2))),1)

The part in red is the first possible range of 12 cells that might be 0. The O2:AL2 range is the possible range of cells that might have 12 zeros preceding it. When we take the COLUMN(O2:AL2)-COLUMN(O2), we get this array: {0,1,2,3, ...}. These are used as offsets to D2:O2, so OFFSET gives us this set of ranges: D2:O2, E2:P2, F2:Q2, etc. Using OFFSET in an array function in Excel is usually a no-no, but there are some exceptions. In this case, you can use OFFSET within COUNTIF within an array function. So the COUNTIF calculates COUNTIF(D2:O2,0), COUNTIF(E2:P2,0), COUNTIF(F2:Q2,0), etc. and the =12 part converts the COUNTIFs to TRUE or FALSE.

=AGGREGATE(14,6,O$1:AL$1/(COUNTIF(OFFSET(D2:O2,0,COLUMN(O2:AL2)-COLUMN(O2)),0)=12)/SIGN((P2:AM2<>0)+(COLUMN(O2:AL2)=COLUMN(AL2))),1)

The red part checks to see if the cell after the range is non-zero. The blue part checks to see if the last cell in each range is the last cell of the entire range. Then the combination of a plus sign between the 2 conditions, and the SIGN function, serves as an OR condition.

What we've done so far is to create an array of dates, divided by 2 conditions: is the preceding range all zeros, and is the next cell non-zero. Since Excel dates are just numbers, it looks like:

44197/TRUE/0, 44198/FALSE/1, 44199/TRUE/1, etc. Dividing anything by 0 (or FALSE) results in a #DIV/0! error, so it turns into:

#DIV/0!, #DIV/0!, 44199, etc.

=AGGREGATE(14,6,O$1:AL$1/(COUNTIF(OFFSET(D2:O2,0,COLUMN(O2:AL2)-COLUMN(O2)),0)=12)/SIGN((P2:AM2<>0)+(COLUMN(O2:AL2)=COLUMN(AL2))),1)

Finally, the AGGREGATE function finds the largest value in that array (code 14), while ignoring any errors (code 6). So it ignores all the #DIIV/0! errors, and returns the largest date where both conditions are TRUE.

Hope this helps!

Now I see why you're real Excel MVP. Thank you!