Formula to find the highest & second highest value in a range of cells

[BigJez82]

G'day Everyone,

I need help to find a formula that will give me the highest & second highest value in a range of cells.

ie. Say the values are 50, 50, 40, 30, 10. I need a formula that will give me 50, and 40.

I have used the LARGE formula to find the highest, and the second highest number. ie =LARGE(range,1) & =LARGE(range,2)

This works fine if there are two distinct values as highest and second highest value. However when there are two values of 50 in the list of values, the formulas return 50 & 50.

Does anyone know a way to achieve this?

Cheers,

Jezza

[windknife]

If you data in A1:A10, try this

=LARGE(A1:A10,COUNTIF(A1:A10,MAX(A1:A10))+1)

[BigJez82]

Thanks very much, windknife! That seems to work.

Cheers

[HansDouwe]

If your data is in A1:A10 and you have Excel 365, please try this:

=TAKE(UNIQUE(SORT(A1:A10,,-1)),2)

[Czeslaw]

Formula

=IFERROR(AGGREGATE(14;6;A$2:A$3500/(COUNTIF(D$1:D1;A$2:A$3500)=0);1);"")

[windknife]

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.