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

[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!

 

[Ron Rosenfeld]

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!

Perhaps:

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

will do what you want?
--ron

 

[Lorne]

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.

 

[JLGWhiz]

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.

 

[JLGWhiz]

Left off the equal sign: =Large(A1:A11, 2)

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.

 

[Ron Rosenfeld]

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.

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