Results 1 to 2 of 2
2005-05-29, 20:55 #1
- Join Date
- May 2005
- Thanked 0 Times in 0 Posts
How do I find the cell address of the 2nd largest
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:
1/8/05.....-2...........16...........30..........-20 (etc) ...
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!
2005-05-30, 01:47 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: How do I find the cell address of the 2nd largest
Welcome to the Lounge.
I do not think that it can be done directly with a formula without some type of intermediate calcs (and even that may be cumbersome). I think your best bet is a "user defiend function", you can use this function, for example:
Function LookupAddress(rng As Range, lValue As Long, _
Optional bLarge As Boolean = True)
Dim lIndex As Long
Dim lRank As Long
Dim dValue As Double
Dim AWF As WorksheetFunction
Dim lCount As Long
Dim vArray() As Variant
Dim rCell As Range
On Error GoTo ErrHandler
Set AWF = Application.WorksheetFunction
If bLarge Then
dValue = AWF.Large(rng, lValue)
lRank = AWF.Rank(dValue, rng, 0)
dValue = AWF.Small(rng, lValue)
lRank = AWF.Rank(dValue, rng, 1)
lIndex = lValue - lRank + 1
ReDim vArray(1 To rng.Cells.Count)
lCount = 0
For Each rCell In rng
If rCell.Value = dValue Then
lCount = lCount + 1
vArray(lCount) = rCell.Address
ReDim Preserve vArray(1 To lCount)
If lCount = 0 Then
LookupAddress = CVErr(xlErrNA)
ElseIf lIndex > lCount Then
LookupAddress = CVErr(xlErrNum)
LookupAddress = vArray(lIndex)
If Err.Number <> 0 Then LookupAddress = CVErr(xlErrValue)
Set AWF = Nothing
Set rCell = Nothing
Add the function to a module and then use something like:
or explicitly for large (the third parameter is optional):
<pre>=LookupAddress(B3:Z51, 2, True)</pre>
to get the address of the 2nd largest in the range B3:Z51 or something like:
<pre>=LookupAddress(B3:Z51, 2, False)</pre>
will give the address of the 2nd smallest