finding minimum between 2 maximum number in an array

[yoni81]

hi,

i have an array of random numbers and i need to find the minimum number between the first highest number and the new highest number that will come later on.

for example 1,2,10,7,5,3,9,2,11 - 10 is the highest, than 11 is the new max so i need to find the min between - 2.

that calculation need to happen more than one time in the array.

thanks!

Yoni

 

[aqeelnokia99]

=AGGREGATE(14,6,{1,2,10,7,5,3,9,2,11},COLUMNS($A$1:A1))
move formula to right toget second third soo on high vale

 

[Eric W]

Welcome to the board.

Are you looking for the minimum value between the 2 highest values? If so, try:

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=MIN(INDEX(A1:I1,MATCH(MAX(A1:I1),A1:I1,0)):INDEX(A1:I1,LOOKUP(2,1/(A1:I1=LARGE(A1:I1,2)),COLUMN(A1:I1))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Removed.
Woluldn't work if both max values were the same.

 

[yoni81]

i need to find it more the one time.
i need to find max 1 and max 2 and than the min 1. after i need to find max 3 and the min 2 between max 2 and max 3. max 2 > max 1 and max 3> max 2
10 2 5 8 3 11 9 4 6 10 1 14
here 10 is max 1 11 is max 2 and 14 is max 3 and i need to know min - 2 (2 min between max 1 and max 2) and min - 1 (1 is min between max 2 and max 3)

thanks!

yoni