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.

[TABLE="width: 118"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Results[/TD]
[TD]Largest[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]82[/TD]
[TD="align: right"]169[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]322[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]322[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]281[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]249[/TD]
[TD="align: right"]281[/TD]
[/TR]
</tbody>[/TABLE]

 

[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


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Results​

[/TD]
[TD]

Largest Values​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

169​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

82​

[/TD]
[TD]

169​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

47​

[/TD]
[TD]

169​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

63​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

112​

[/TD]
[TD]

169​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

57​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

42​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

322​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

208​

[/TD]
[TD]

322​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

28​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

39​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

281​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

249​

[/TD]
[TD]

322​

[/TD]
[/TR]
</tbody>[/TABLE]


with CSE i got the expected results


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Results​

[/TD]
[TD]

Largest Values​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

169​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

82​

[/TD]
[TD]

169​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

47​

[/TD]
[TD]

47​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

63​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

112​

[/TD]
[TD]

112​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

57​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

42​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

322​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

208​

[/TD]
[TD]

322​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

28​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

39​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

281​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

249​

[/TD]
[TD]

281​

[/TD]
[/TR]
</tbody>[/TABLE]


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