Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I find the cell address of the 2nd largest

    Hello.

    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!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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:

    <pre>Option Explicit
    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)
    Else
    dValue = AWF.Small(rng, lValue)
    lRank = AWF.Rank(dValue, rng, 1)
    End If
    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
    End If
    Next

    ReDim Preserve vArray(1 To lCount)
    If lCount = 0 Then
    LookupAddress = CVErr(xlErrNA)
    ElseIf lIndex > lCount Then
    LookupAddress = CVErr(xlErrNum)
    Else
    LookupAddress = vArray(lIndex)
    End If
    ErrHandler:
    If Err.Number <> 0 Then LookupAddress = CVErr(xlErrValue)
    Set AWF = Nothing
    Set rCell = Nothing
    End Function</pre>


    Add the function to a module and then use something like:
    <pre>=LookupAddress(B3:Z51, 2)</pre>

    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

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •