# Find highest value in sequences of numbers

#### ddub25

##### Well-known Member
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

##### MrExcel MVP
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### pgc01

##### MrExcel MVP
Hi Marcelo

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

#### Marcelo Branco

##### MrExcel MVP
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

##### MrExcel MVP
... sometimes we do not see the obvious.

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

#### Ingolf

##### Banned

@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

##### MrExcel MVP
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

##### MrExcel MVP

Hi

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

#### pgc01

##### MrExcel MVP
Hi

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

#### Marcelo Branco

##### MrExcel MVP
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

##### Well-known Member
=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

Replies
10
Views
147
Replies
29
Views
1K
Replies
0
Views
398
Replies
31
Views
971
Replies
2
Views
251

1,141,315
Messages
5,705,699
Members
421,406
Latest member
kluna90

### 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.

### Which adblocker are you using?

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

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