Find more than one maximum value before a zero

sayseee

New Member
Joined
Nov 25, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
TUV
1NUMNo ZerosWith zeros
2022
3166
420
50
61
72
83
94
105
116
120
Sheet2
Cell Formulas
RangeFormula
U2:U3U2=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:V4V2=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.
 
Upvote 0
@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
AB
112
226
30 
41 
52
63
74
85
96
100
Before 0
Cell Formulas
RangeFormula
B1:B4B1=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10)/(A$1:A$10=0),ROWS(B$1:B1))-1),"")
 
Upvote 0
@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
AB
112
226
30 
41 
52
63
74
85
96
100
Before 0
Cell Formulas
RangeFormula
B1:B4B1=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
MNOPQR
2FixtureResultWCO
3GuadalupevAlajuelense2 - 4 W1
4AlajuelensevGuadalupe1 - 0 W2
5GuadalupevAlajuelense1 - 3 W3
6GuadalupevAlajuelense1 - 2 W4
7AlajuelensevGuadalupe1 - 1 D0
8GuadalupevAlajuelense1 - 1 D0
9AlajuelensevGuadalupe2 - 3 L0
10AlajuelensevGuadalupe1 - 2 L0
11GuadalupevAlajuelense0 - 1 W1
12AlajuelensevGuadalupe3 - 0 W2
Sheet5
Cell Formulas
RangeFormula
R3R3=IF(Q3="W",AH2+1,0)
R4:R12R4=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
 
Upvote 0
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
TUV
1NUMNo ZerosWith zeros
2022
3166
420
50
61
72
83
94
105
116
120
Sheet2
Cell Formulas
RangeFormula
U2:U3U2=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:V4V2=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
 
Upvote 0
Try this modification of my previous formula.

21 11 26.xlsm
RS
1
2
314
422
53 
64 
70
80
90
100
111
122
13
Before 0
Cell Formulas
RangeFormula
S3:S6S3=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),"")
 
Upvote 0
Solution
Th
Try this modification of my previous formula.

21 11 26.xlsm
RS
1
2
314
422
53 
64 
70
80
90
100
111
122
13
Before 0
Cell Formulas
RangeFormula
S3:S6S3=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.
 
Upvote 0
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),"")
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top