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]

Adding one more screenshot:

 

[KOKOSEK]

Try:

=INDEX($D$1:$AL$1,MATCH(2,1/(D2:AL2=0),1))

p.s. when I was replying you've pasted new picture, so I've got question related. Why in row 9 last 0 is in col X not in AL?

 

[Dempix]

Try:

=INDEX($D$1:$AL$1,MATCH(2,1/(D2:AL2=0),1))

p.s. when I was replying you've pasted new picture, so I've got question related. Why in row 9 last 0 is in col X not in AL?

Thank you. But this formula doesn't work, since formula doesn't take into account 12 consecutive 0:

In this case 12 consecutive zeros end at X9 and the corresponding date is 2020-09, but formula gives 2021-11 for me (the last zero in the row)

 

[KOKOSEK]

So you want to ignore fact that later in row there are another some consecutive zeros?
In fact, formula finding last 0 even if not consecutive. How many 0 means consecutive? 2, I guess, so in case like 1110020000000000000000 you want to get position 5.

 

[jdellasala]

Assuming you want Column C to contain the date of the last column where (a sequence of) 12 or more 0's appear (and row 1 extends to column AM), the formula in Cell C2 would be:

=IF(COUNTIF(D2:AM2,0)>=12,MAXIFS($D$1:$AM$1,D2:AM2,0),"None")

NOTE: This only works assuming there won't be ANY non-sequential 0's in a row (the image provided doesn't look like it). If there were, the formula would still return the date of the column with the last 0 in the row. I tried using

=IF(SUMPRODUCT(COUNTIF(D29:AM29,SEQUENCE(1,12,0,0)))>132,MAXIFS($D$1:$AM$1,D29:AM29,0),"None")

using SUMPRODUCT to avoid CSE in older versions of Excel (SUM works in 365/2021) and 132 is the result if there are 11 0's, but stray 0's are still being counted in spite of it not matching the sequence provided. Perhaps someone can refine this?
By coincidence, this video was posted yesterday: Find The Last One In A Series Of 1s And Zeroes With Dan Mayoh - 2445

 

[KOKOSEK]

@jdellasala: S6 and S7 = 0

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1				1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/9/2021	1/10/2021	1/11/2021	1/12/2021	1/13/2021	1/14/2021	1/15/2021	1/16/2021	1/17/2021	1/18/2021	1/19/2021	1/20/2021	1/21/2021	1/22/2021	1/23/2021	1/24/2021	1/25/2021	1/26/2021	1/27/2021	1/28/2021	1/29/2021	1/30/2021	1/31/2021	2/1/2021	2/2/2021	2/3/2021	2/4/2021
2				1	2	3	4	5	6	7	8	9	10	11	12	0	0	0	0	0	0	0	0	0	0	0	0	1	2	3	0	0	0	0	0	0	0	0		1/24/2021
3				0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1/12/2021
4				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0		2/4/2021
5				2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2		#NUM!
Sheet8

Cell Formulas
Range	Formula
AN2:AN5	AN2	=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)

 

[Dempix]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1				1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/9/2021	1/10/2021	1/11/2021	1/12/2021	1/13/2021	1/14/2021	1/15/2021	1/16/2021	1/17/2021	1/18/2021	1/19/2021	1/20/2021	1/21/2021	1/22/2021	1/23/2021	1/24/2021	1/25/2021	1/26/2021	1/27/2021	1/28/2021	1/29/2021	1/30/2021	1/31/2021	2/1/2021	2/2/2021	2/3/2021	2/4/2021
2				1	2	3	4	5	6	7	8	9	10	11	12	0	0	0	0	0	0	0	0	0	0	0	0	1	2	3	0	0	0	0	0	0	0	0		1/24/2021
3				0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1/12/2021
4				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0		2/4/2021
5				2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2		#NUM!
Sheet8

Cell Formulas
Range	Formula
AN2:AN5	AN2	=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)

Thank you. Formula work perfectly.

But maybe you can explain what each part of formula does (because I tried replicating it on a range with more columns and it doesn't work properly)?

 

[Eric W]

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!