Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mr. Snrub
 
Posts: n/a
Default How do I find the cell address of the 2nd largest of a set?

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
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.



Mr. Snrub wrote:

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
  #3   Report Post  
 
Posts: n/a
Default

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.

  #4   Report Post  
 
Posts: n/a
Default

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.

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.)



wrote:

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use a function to return the address of a cell? ren6175 Excel Worksheet Functions 6 April 21st 05 03:13 PM
Formula to return ADDRESS of cell in range that meets criteria Christie Excel Worksheet Functions 1 March 5th 05 12:13 AM
Find Max and Min based on cell reference gregork Excel Discussion (Misc queries) 3 February 21st 05 01:28 AM
How do I dynamically retrieve the cell address of the last cell t. Nancy Excel Discussion (Misc queries) 1 December 20th 04 03:52 PM
How to find highest, lowest and last cell in row? Sam Excel Discussion (Misc queries) 3 December 3rd 04 12:59 PM


All times are GMT +1. The time now is 05:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"