Find highest value in sequences of numbers

[ddub25]

In the table below I want to analyse the 'Results' column for the highest value in each sequence of positive numbers between the 0's. For example, in the first 3 rows there are values of 50, 169, 82 before a 0 appears. 169 is the highest value and so I want this value to be returned in the 'Largest Values' column. In row 5 of 'Results', the sequence starts again (after a 0) with a value of 47, but only lasts one row before a 0 appears and so 47 would be returned.

The largest values don't have to be returned at the bottom of their sequence of positive numbers, this is just where I have put them for ease of communication. They could just be outputted starting in row 1 of 'Largest Values' - which ever is the easiest way to do it.

Having tried different formulas, I can't come up with anything sophisticated enough to do the job. Can anyone point me in the right direction?

Thanks.

Results	Largest
	Values
50
169
82	169
0
47	47
0
63
112	112
0
57
42
322
208	322
0
0
0
103
28
39
281
249	281

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

pgc

I think you mean

B2 (just adjusting the ranges to suit)
=IF(AND(A2<>0,A3=0),MAX(INDEX(A:A,1+IFERROR(MATCH(2,1/(A$1:A1=0)),1)):A2),"")

M.

 

[pgc01]

Hi Marcelo

Thank you. I copied the formula from B4 instead of from B2.

 

[Marcelo Branco]

Hi Pedro,

Yes I noticed that you copied the formula from row 4.
Your formula is much simpler. My formula was unnecessarily complicated - sometimes we do not see the obvious.

M.

 

[pgc01]

... sometimes we do not see the obvious.

The "sometimes" meaning (for me) everyday several times!

 

[Ingolf]

@pgc01, @Marcelo Branco

You have to mention that this excellent formula:

=IF(AND(A2<>0,A3=0),MAX(INDEX(A:A,1+IFERROR(MATCH(2,1/(A$1:A1=0)),1)):A2),"")

is CSE formula.

 

[Marcelo Branco]

Ingolf

You are right, the formula above requires CSE.

Alternatively i suggest this new version (no CSE)
B2 copied down
=IF(AND(A2<>0,A3=0),MAX(INDEX(A:A,1+IFERROR(LOOKUP(2,1/(A$1:A1=0),ROW(A$1:A1)),1)):A2),"")

M.

 

[pgc01]

Hi

Not able to test now, but are you sure the formula I posted requires CSE?

 

[pgc01]

Hi

Just tested. Didn't seem to need CSE. Could you post an example where it's needed?

 

[Marcelo Branco]

Hi Pedro

Am i missing the obvious? (maybe using a wrong formula)

I tested without CSE (see post #11)
B2
=IF(AND(A2<>0,A3=0),MAX(INDEX(A:A,1+IFERROR(MATCH(2,1/(A$1:A1=0)),1)):A2),"")

and the results were

	A​	B​
1​	Results​	Largest Values​
2​	50​
3​	169​
4​	82​	169​
5​	0​
6​	47​	169​
7​	0​
8​	63​
9​	112​	169​
10​	0​
11​	57​
12​	42​
13​	322​
14​	208​	322​
15​	0​
16​	0​
17​	0​
18​	103​
19​	28​
20​	39​
21​	281​
22​	249​	322​

<tbody>
</tbody>

with CSE i got the expected results

	A​	B​
1​	Results​	Largest Values​
2​	50​
3​	169​
4​	82​	169​
5​	0​
6​	47​	47​
7​	0​
8​	63​
9​	112​	112​
10​	0​
11​	57​
12​	42​
13​	322​
14​	208​	322​
15​	0​
16​	0​
17​	0​
18​	103​
19​	28​
20​	39​
21​	281​
22​	249​	281​

<tbody>
</tbody>

M.

 

[XOR LX]

=IF(AND(A2<>0,A3=0),MAX(INDEX(A:A,1+IFERROR(MATCH(2,1/(A$1:A1=0)),1)):A2),"")

That will indeed require CSE.

Regards