ExcelBanter - View Single Post - How to pick the second largest value in a data set?

Ron Rosenfeld

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