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

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!

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

On Tue, 20 Nov 2007 15:11:43 -0800 (PST), wrote:

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

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

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.

wrote in message
...
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!

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

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.

" wrote:

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!

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

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

"JLGWhiz" wrote:

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.

" wrote:

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!

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

On Tue, 20 Nov 2007 18:28:00 -0800, JLGWhiz
wrote:

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