Finding a Large Value in Excel

I got an interesting question from Marci. She wanted to know how to find the second largest and third largest numbers in three ranges of numbers. Marci wrote:

Good afternoon!

Can you tell me in Excel, if I want to find the Largest, Second Largest, Third Largest numbers in multiple series, how would I write the formula. For one series, to find the second largest number, it would be:
=Large(A2:E2,3)


If I want to find the second largest number in multiple series, how would I write this? For example: I want to know the second largest number of all the following cells:

A2:A20
C2:C20
E2:E20

=Large((A2:A20)(C2:C20)(E2:E20)),2))) What am I doing wrong?

Thank you!

I wrote back to Marci to suggest a couple alternatives. I'm not sure you can do this directly in Excel (and she didn't mention which Excel version she was using).

First, if columns B and D are blank or have non-numeric values, she could just define a range that crosses all the columns. In that case, she could use =large(a2.e20,2) to get the second largest number.

However, I'm assuming that she has some other values in columns B and D that might interfere with the function.

In that case, I'd try to do it by "cheating" — let's call it "Thinking Outside the Box." Let's duplicate the data to be searched so that the three columns of data are together. This could be elsewhere on the spreadsheet, or it could be in another "worksheet" within the Excel spreadsheet.

First, in 3 other columns, for this purpose let's use columns L, M and N, I would duplicate the values in the A, C and E ranges. The nice thing about Excel is that we can use the + formula to duplicate the value, whether the initial value was a number or text.

So, I'd make cell M2 =+a2, cell L3 =+A3,... cell L20 =+A20
   then,
cell M3 =+C3... cell M20 =+C20
   and
=+E3... cell N20 =+E20

At that point, we can calculate the second largest number using the Excel Large function, as =large(M2.O20,2)

Alternatively, she could create one long column and use it to test, as in
m2=a2...m20=a20, m21=c2...m40=c20...
However, that solution would be a one-time solution.

I like to build open-ended solutions so that the next time I need to manipulate similar data, all I have to do is copy in the new data and, perhaps, copy the formulas to some additional rows.

Copyright © 2009 Terry A. Stockdale. All rights reserved.