Find more than one maximum value before a zero

[sayseee]

Hello everyone
I'm trying to use the formula =MAX(FREQUENCY(IF(T2:T11>1,COLUMN(T2:T11)),IF(T2:T11>=1,COLUMN(T2:T11)))) to find more than one maximum value before a zero from a range.

Column T has this values

1​

2​

0​

1​

2​

3​

4​

5​

6​

0​

I want a formula/formulas that will return 2 and 6

Thank you

 

[bstory84]

Possibly this. I feel like there is an easier way, but could not figure it out. So here is the long way.
The formula could be shorter if you are ok with zero's.

Book1
	T	U	V
1	NUM	No Zeros	With zeros
2	0	2	2
3	1	6	6
4	2		0
5	0
6	1
7	2
8	3
9	4
10	5
11	6
12	0
Sheet2

Cell Formulas
Range	Formula
U2:U3	U2	=FILTER(UNIQUE(INDEX(T2:T12,IF(T2:T12=0,ROW(T2:T12)-2),1),FALSE,FALSE),UNIQUE(INDEX(T2:T12,IF(T2:T12=0,ROW(T2:T12)-2),1),FALSE,FALSE)>0,"")
V2:V4	V2	=UNIQUE(INDEX(U2:U12,IF(U2:U12=0,ROW(U2:U12)-2),1),FALSE,FALSE)
Dynamic array formulas.

All the "FALSE" values in the formula can also be replaced with a 0.

 

[Peter_SSs]

@bstory84
If you look under the OP's user name at the left of their post you will see that they are using Excel 2019 & hence will not have access to the FILTER or UNIQUE functions that you have suggested for them.

@sayseee
Welcome to the MrExcel board!

If your sample data is representative and the max value before each zero is in the cell immediately above that zero then try the formula below.
If your data is not representative of your real data, then please post some that is, and again include the expected results.
BTW, I suggest that you investigate XL2BB for providing sample data.

21 11 26.xlsm
	A	B
1	1	2
2	2	6
3	0
4	1
5	2
6	3
7	4
8	5
9	6
10	0
Before 0

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10)/(A$1:A$10=0),ROWS(B$1:B1))-1),"")

 

[sayseee]

@bstory84
If you look under the OP's user name at the left of their post you will see that they are using Excel 2019 & hence will not have access to the FILTER or UNIQUE functions that you have suggested for them.

@sayseee
Welcome to the MrExcel board!

If your sample data is representative and the max value before each zero is in the cell immediately above that zero then try the formula below.
If your data is not representative of your real data, then please post some that is, and again include the expected results.
BTW, I suggest that you investigate XL2BB for providing sample data.

21 11 26.xlsm
	A	B
1	1	2
2	2	6
3	0
4	1
5	2
6	3
7	4
8	5
9	6
10	0
Before 0

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10)/(A$1:A$10=0),ROWS(B$1:B1))-1),"")

Hello Peter thanks for your input. below is my real sample data

BD6.xlsb
	M	N	O	P	Q	R
2	Fixture			Result		WCO
3	Guadalupe	v	Alajuelense	2 - 4	W	1
4	Alajuelense	v	Guadalupe	1 - 0	W	2
5	Guadalupe	v	Alajuelense	1 - 3	W	3
6	Guadalupe	v	Alajuelense	1 - 2	W	4
7	Alajuelense	v	Guadalupe	1 - 1	D	0
8	Guadalupe	v	Alajuelense	1 - 1	D	0
9	Alajuelense	v	Guadalupe	2 - 3	L	0
10	Alajuelense	v	Guadalupe	1 - 2	L	0
11	Guadalupe	v	Alajuelense	0 - 1	W	1
12	Alajuelense	v	Guadalupe	3 - 0	W	2
Sheet5

Cell Formulas
Range	Formula
R3	R3	=IF(Q3="W",AH2+1,0)
R4:R12	R4	=IF(Q4="W",R3+1,0)

Using that sample data, You will notice that Alajuelense had 4 consecutive wins and then at some point it also had 2 consecutive wins. So I need a formula that can get for me the 4 in (R6) and 2 in (R12). Hope this will be helpful. Thank you

 

[sayseee]

Possibly this. I feel like there is an easier way, but could not figure it out. So here is the long way.
The formula could be shorter if you are ok with zero's.

Book1
	T	U	V
1	NUM	No Zeros	With zeros
2	0	2	2
3	1	6	6
4	2		0
5	0
6	1
7	2
8	3
9	4
10	5
11	6
12	0
Sheet2

Cell Formulas
Range	Formula
U2:U3	U2	=FILTER(UNIQUE(INDEX(T2:T12,IF(T2:T12=0,ROW(T2:T12)-2),1),FALSE,FALSE),UNIQUE(INDEX(T2:T12,IF(T2:T12=0,ROW(T2:T12)-2),1),FALSE,FALSE)>0,"")
V2:V4	V2	=UNIQUE(INDEX(U2:U12,IF(U2:U12=0,ROW(U2:U12)-2),1),FALSE,FALSE)
Dynamic array formulas.

All the "FALSE" values in the formula can also be replaced with a 0.

Thanks for the help, unfortunately am using Excel 2019 and I believe your solution would work best with 365. Thank you

 

[Peter_SSs]

Try this modification of my previous formula.

21 11 26.xlsm
	R	S
1
2
3	1	4
4	2	2
5	3
6	4
7	0
8	0
9	0
10	0
11	1
12	2
13
Before 0

Cell Formulas
Range	Formula
S3:S6	S3	=IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$3:R$100)/((R$3:R$1100=0)*(R$2:R99<>0)),ROWS(S$3:S3))-1),"")

 

[sayseee]

Th

Try this modification of my previous formula.

21 11 26.xlsm
	R	S
1
2
3	1	4
4	2	2
5	3
6	4
7	0
8	0
9	0
10	0
11	1
12	2
13
Before 0

Cell Formulas
Range	Formula
S3:S6	S3	=IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$3:R$100)/((R$3:R$1100=0)*(R$2:R99<>0)),ROWS(S$3:S3))-1),"")

Thumbs up Peter. Thank you so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Peter_SSs]

I just realised that there are two typos in my formula in post #6. They may not affect your results and/or you may have corrected them anyway.

The 1100 shown below is incorrect
=IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$3:R$100)/((R$3:R$1100=0)*(R$2:R99<>0)),ROWS(S$3:S3))-1),"")

That was supposed to be 100 as the two ranges shown should be the same size as each other but just offset by one row.
Also, the second of those ranges was missing a $ sign before the 99
=IFERROR(INDEX(R:R,AGGREGATE(15,6,ROW(R$3:R$100)/((R$3:R$100=0)*(R$2:R$99<>0)),ROWS(S$3:S3))-1),"")