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!

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

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!

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!

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!

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