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
1	1	2	10	7	5	3	9	2	11
2
3									2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula I3 =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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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