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]

Maybe something like this


[Table="class: grid"][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]
[/table]

Formula in 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)):INDEX(A2:A$1000,IFERROR(MATCH(0,A2:A$1000,0)-1,1))),"")

Hope this helps

M.

 

[pjmorris]

Theres possibly a better way, but I cant think of it, but this should work:

1. in cell B1: =A1
2. In cell B2: =IF(A2<>0,MAX(B1,A2),0)
3. In cell C2: =IF(A3=0,IF(B2<>0,B2,""),"")

Copy cells B2 and C2 down the column as far as possible.

Column B keeps a running total of the maximums between 0's and column C simply extracts the maximum before the zero.

HTH

 

[pjmorris]

@Marcelo Branco: I knew there would be a more elegant solution!

 

[ddub25]

Thanks for both of your help. Yes, I went for Marcelo solution, so thanks Marcelo - struggling to get my head round how it actually works, but it does the job. Thanks again.

 

[pjmorris]

Delighted to have helped and absolutely no probs with going with Marcelo's solution, it avoids the helper column.

Thanks for the feedback. One thought on understanding Marcelo's solution is to use the Evaluate Formula option of the Audit tab on the Formula ribbon; it allows you to step through how the formula works and to see the interim results.

HTH

 

[Marcelo Branco]

Thanks for both of your help. Yes, I went for Marcelo solution, so thanks Marcelo - struggling to get my head round how it actually works, but it does the job. Thanks again.

You are welcome. Glad to help

M.

 

[ddub25]

Thanks Peter, that's very useful to know - I didn't realise Evaluate Formula existed. I always end up manually breaking up formulas to understand them. This will save me a lot of time. Thanks.

Dan

 

[pjmorris]

No problem, delighted to have helped. Thanks for the feedback.

 

[pgc01]

Another option, in B2:

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