How to pick the second largest value in a data set?

  • Thread starter Thread starter loosen
  • Start date Start date
L

loosen

I'm working on a spreadsheet now, and I'm trying to pull out the
second largest value from a set of numbers. For instance, let's say
that I have this series of numbers:

10
8
10
7
10
8
5
4
1
1
4

I want to know that the second largest number is an eight.

By using MAX, I was able to get that the largest number is 10, but I
couldn't find a way to give me the second maximum number.

I tried LARGE, but Excel won't accept =LARGE(A1:A11, "<10") as an
argument in this case.

Is there another way that I can perform this function? It seems
rather simple, but I couldn't find anything on it in the books.

Thanks!
 
I'm working on a spreadsheet now, and I'm trying to pull out the
second largest value from a set of numbers. For instance, let's say
that I have this series of numbers:

10
8
10
7
10
8
5
4
1
1
4

I want to know that the second largest number is an eight.

By using MAX, I was able to get that the largest number is 10, but I
couldn't find a way to give me the second maximum number.

I tried LARGE, but Excel won't accept =LARGE(A1:A11, "<10") as an
argument in this case.

Is there another way that I can perform this function? It seems
rather simple, but I couldn't find anything on it in the books.

Thanks!
Click to expand...


Perhaps:

=LARGE(rng,COUNTIF(rng,MAX(rng))+1)

will do what you want?
--ron
 
You could write a macro to do it.

Else try adding an extra column with a formula like =if(a1=max($a$1..$A$10),
0, A1) and max that column.
 
I believe that what you want is:

Large(A1:A11, 2)

This returns the second largest value in the range,

Large(A1:A11,3) returns the third largest, etc.
 
Left off the equal sign: =Large(A1:A11, 2)

JLGWhiz said:
I believe that what you want is:

Large(A1:A11, 2)

This returns the second largest value in the range,

Large(A1:A11,3) returns the third largest, etc.
Click to expand...
 
I believe that what you want is:

Large(A1:A11, 2)

This returns the second largest value in the range,

Large(A1:A11,3) returns the third largest, etc.
Click to expand...



In XL2002, your formulas for 2nd and 3rd largest, run against the OP's data,
both return "10". This was not what the OP requested, although it is how I
understand the LARGE worksheet function to work.


--ron
 
Back
Top