Find highest value in sequences of numbers

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
594
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.

ResultsLargest
Values
50
169
82169
0
4747
0
63
112112
0
57
42
322
208322
0
0
0
103
28
39
281
249281

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,860
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884
Hi Marcelo

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,860
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Ingolf

Banned
Joined
Mar 20, 2011
Messages
809

ADVERTISEMENT

@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
Joined
Aug 23, 2010
Messages
16,860
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,860
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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
Top