second best

[paj*]

Hi

How can I get the second best or second worst value out of an array?

I am searching for something like min(array; position(e.g. 2nd)).

Thanks in advance

paj*

 

[Jonmo1]

Use the LARGE Function for second best(largest) and SMALL for 2nd Works (smallest)

x = worksheetfunction.large(range("a:a"),2)

or

x = worksheetfunction.small(range("a:a"),2)

 

[Scott Huish]

If you have duplicates and don't want to count the duplicates.
For example if the highest number is 4 but there are two fours in this column, and down't want to count the second 4 as the second highest number, perhaps:

For the 2nd highest number:
=LARGE(G1:G5,1+COUNTIF(G1:G5,MAX(G1:G5)))

For the 2nd lowest number:
=SMALL(G1:G5,1+COUNTIF(G1:G5,MIN(G1:G5)))

 

[paj*]

Thanks mates

But how can I do the following

I got 3 cells, one for the best, second for the second, third for the guess what?

All my values are in the array
g1:g10

no I got 3 numbers having the same value

how can I achieve that they all 3 are displayed in those 3 cells? In this case order doesn't matter, but I want to display all 3 as they are the best three

 

[Scott Huish]

Best:
=MAX(G1:G10) or =LARGE(G1:G10,1)

2nd Best
=LARGE(G1:G10,2)

3rd Best
=LARGE(G1:G10,3)