How do I find the cell address of the 2nd largest of a set?

[Mr. Snrub]

I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the second-largest and third-largest value.

=LARGE(B3:Z51, 2) will give me the second-largest value, but how do I find
the cell address where that value is located?

Also, when there is the exact same value in two different cells, I want to
be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will find
the third-largest value, and LARGE(B3:Z51, 4) will find the fourth-largest
value. If those two values are the same, how do I find their corresponding
addresses?

See, what I'm doing with this data is the ten highest values will be put in
another worksheet as a sort of "top ten list".

The format looks a little something like this:

Date.......Alex.........Becky......Carl........Don
1/1/05.....23...........47..........-29.........21
1/8/05.....-2...........16...........30..........-20 (etc) ...
2/2/05.....30..........-50..........40...........65
2/14/05...14..........-20..........15...........34
....

It goes on like that for a long time. With my Top Ten List, I want to have
the value along with the date and the person's name, and the only way I can
find the corresponding date or person's name is if I know the cell address.

Please help me!

Senor Snrub

 

[Dave Peterson]

One way (with your data in B2:E5:

=ADDRESS(MIN(IF($B$2:$E$5=LARGE($B$2:$E$5,2),ROW($B$2:$E$5))),
MIN(IF($B$2:$E$5=LARGE($B$2:$E$5,2),COLUMN($B$2:$E$5))))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match.

 

[tkt_tang]

To repeat Senor Snrub :

"Also, when there are the exact same values in two different cells, I
want to
be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will
find
the third-largest value, and LARGE(B3:Z51, 4) will find the
fourth-largest
value. If those two values are the same, how do I find their
corresponding
addresses?"

I can sympathize ; I've struggled just the same. Regards.

 

[tkt_tang]

To repeat Senor Snrub :

"Also, when there are the exact same values in two different cells, I
want to
be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will
find
the third-largest value, and LARGE(B3:Z51, 4) will find the
fourth-largest
value. If those two values are the same, how do I find their
corresponding
addresses?"

I can sympathize ; I've struggled just the same. Regards.

 

[Dave Peterson]

Oops. I didn't see that.

I'd use a second worksheet and use that to determine the ranks of each value
(with a little fudge factor added to each to make each unique).

I put my test data in A1:C3 of sheet1:

5 5 5
7 6 8
3 9 1

I put this formula in A1:C3 of Sheet2:
=RANK(Sheet1!A1:C3,Sheet1!$A$1:$C$3)
+ROW(Sheet1!A1:C3)/1000+COLUMN(Sheet1!A1:C3)/1000000

(It just adds a little bit to each _integer_ value in that range)

5.001001 5.001002 5.001003
3.002001 4.002002 2.002003
8.003001 1.003002 9.003003

Then this array formula worked ok (on the helper sheet):
=ADDRESS(MIN(IF($A$1:$C$3=LARGE($A$1:$C$3,2),ROW($A$1:$C$3))),
MIN(IF($A$1:$C$3=LARGE($A$1:$C$3,2),COLUMN($A$1:$C$3))))

If it were me, I'd use the exact same range addresses on both the "real"
worksheet and the "helper" worksheet.

(Yes, this breaks if there are existing decimal values that could interfere with
my fudge factor.)