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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

loosen@goatstore.com - 20 Nov 2007 23:11 GMT

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!

Reply to this Message

Ron Rosenfeld - 20 Nov 2007 23:31 GMT

>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
[quoted text clipped - 24 lines]
>
>Thanks!

Perhaps:

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

will do what you want?
--ron

Reply to this Message

Lorne - 20 Nov 2007 23:34 GMT

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'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
[quoted text clipped - 24 lines]
>
> Thanks!

Reply to this Message

JLGWhiz - 21 Nov 2007 02:28 GMT

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.

> 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
[quoted text clipped - 24 lines]
>
> Thanks!

Reply to this Message

JLGWhiz - 21 Nov 2007 02:52 GMT

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

> I believe that what you want is:
>
[quoted text clipped - 32 lines]
> >
> > Thanks!

Reply to this Message

Ron Rosenfeld - 21 Nov 2007 12:33 GMT

>I believe that what you want is:
>
[quoted text clipped - 3 lines]
>
>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

Reply to this Message