Closed Thread
Results 1 to 14 of 14

[SOLVED] How do I find address of cell containing maximum value

  1. #1
    Doug
    Guest

    [SOLVED] How do I find address of cell containing maximum value

    I'd like to have the cell address returned along with a value when I use the
    MAX function. Is there a way to do that?

  2. #2
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Quote Originally Posted by Doug
    I'd like to have the cell address returned along with a value when I use the
    MAX function. Is there a way to do that?
    Assuming that your entries are in Cells A1:A100, you can do it this way ...

    In Cell B1, the formula =max(A1:A100) and

    in Cell B2, the cell location of the maximum number in the range (shown in B1) is given by the formula

    =CELL("address",INDEX(A1:A100,MATCH(MAX(A1:A100),A1:A100
    ,0)))


    BTW, if you do not want to know what the maximum number is, you can simply go directly to entering the formula in B2.

    Regards and hope this will help you.
    BenjieLop
    Houston, TX

  3. #3
    Ashish Mathur
    Guest

    RE: How do I find address of cell containing maximum value

    Hi,

    To get address, use the following formula

    ADDRESS(MATCH(MAX(A1:A7),$A$1:A7,0),1)

    Regards,

    Ashish Mathur

    "Doug" wrote:

    > I'd like to have the cell address returned along with a value when I use the
    > MAX function. Is there a way to do that?


  4. #4
    Ron Rosenfeld
    Guest

    Re: How do I find address of cell containing maximum value

    On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <Doug@discussions.microsoft.com>
    wrote:

    >I'd like to have the cell address returned along with a value when I use the
    >MAX function. Is there a way to do that?


    With the range in which the numbers are stored named "rng" (or you may
    substitute the cell reference directly in the formula, the following **array**
    formula will give you the cell address of the first cell to contain that
    maximum value.

    To enter an *array* formula, after typing or pasting in the formula, hold down
    <ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
    formula.

    =ADDRESS(MAX((rng=MAX(rng))*ROW(rng)),
    MAX((rng=$A$1)*COLUMN(rng)))

    This will work for ranges comprising multiple rows/columns as well as for
    ranges which are just a single row or column.

    However, the range may not include more than 65,535 cells.

    If that is a requirement, a VBA solution will probably be needed.


    --ron

  5. #5
    Harlan Grove
    Guest

    Re: How do I find address of cell containing maximum value

    "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote...
    >With the range in which the numbers are stored named "rng" (or you
    >may substitute the cell reference directly in the formula, the
    >following **array** formula will give you the cell address of the
    >first cell to contain that maximum value.

    ....
    >=ADDRESS(MAX((rng=MAX(rng))*ROW(rng)),
    >MAX((rng=$A$1)*COLUMN(rng)))
    >
    >This will work for ranges comprising multiple rows/columns as well as
    >for ranges which are just a single row or column.
    >
    >However, the range may not include more than 65,535 cells.

    ....

    Multiple inaccuracies. Last first - rng can't span entire columns, but could
    span 65,535 rows in multiple columns. Won't be fast to recalc, but will
    work.

    Secondly, this could return incorrect results when there are multiple
    instances of the maximum value, e.g., A1:C4 containing

    2 1 8
    3 4 5
    6 7 3
    8 5 0

    If that were rng, your formula would return $C$4, which happens to be the
    minimum value.

    Searching 2D ranges *REQUIRES* specifying whether to search along columns
    then rows or along rows then columns. Also, formulas calling ADDRESS are
    overly redundant. CELL("Address",INDEX(rng,...)) is the way to go.



  6. #6
    Alan Beban
    Guest

    Re: How do I find address of cell containing maximum value

    Harlan Grove wrote:
    > "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote...
    >
    >>With the range in which the numbers are stored named "rng" (or you
    >>may substitute the cell reference directly in the formula, the
    >>following **array** formula will give you the cell address of the
    >>first cell to contain that maximum value.

    >
    > ...
    >
    >>=ADDRESS(MAX((rng=MAX(rng))*ROW(rng)),MAX((rng=$A$1)*COLUMN(rng)))
    >>
    >>This will work for ranges comprising multiple rows/columns as well as
    >>for ranges which are just a single row or column.
    >>
    >>However, the range may not include more than 65,535 cells.

    >
    > ...
    >
    > Multiple inaccuracies. Last first - rng can't span entire columns, but could
    > span 65,535 rows in multiple columns. Won't be fast to recalc, but will
    > work.
    >
    > Secondly, this could return incorrect results when there are multiple
    > instances of the maximum value, e.g., A1:C4 containing
    >
    > 2 1 8
    > 3 4 5
    > 6 7 3
    > 8 5 0
    >
    > If that were rng, your formula would return $C$4, which happens to be the
    > minimum value.


    When I did it it returned $A$4.

    Alan Beban

  7. #7
    Ron Rosenfeld
    Guest

    Re: How do I find address of cell containing maximum value

    On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:

    >"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote...
    >>With the range in which the numbers are stored named "rng" (or you
    >>may substitute the cell reference directly in the formula, the
    >>following **array** formula will give you the cell address of the
    >>first cell to contain that maximum value.

    >...
    >>=ADDRESS(MAX((rng=MAX(rng))*ROW(rng)),
    >>MAX((rng=$A$1)*COLUMN(rng)))
    >>
    >>This will work for ranges comprising multiple rows/columns as well as
    >>for ranges which are just a single row or column.
    >>
    >>However, the range may not include more than 65,535 cells.

    >...
    >
    >Multiple inaccuracies. Last first - rng can't span entire columns, but could
    >span 65,535 rows in multiple columns. Won't be fast to recalc, but will
    >work.


    I thought it could. But when I tried it I got an error message that I thought
    was due to that issue. It seems, however, that the error was due to a
    different problem.

    >
    >Secondly, this could return incorrect results when there are multiple
    >instances of the maximum value, e.g., A1:C4 containing
    >
    >2 1 8
    >3 4 5
    >6 7 3
    >8 5 0
    >
    >If that were rng, your formula would return $C$4, which happens to be the
    >minimum value.
    >


    Actually, when I use your data with rng A1:C4, the formula returns A4.

    But there do seem to be instances where an error message is returned.


    >Searching 2D ranges *REQUIRES* specifying whether to search along columns
    >then rows or along rows then columns. Also, formulas calling ADDRESS are
    >overly redundant. CELL("Address",INDEX(rng,...)) is the way to go.
    >


    Hmmm, I'll have to remember that.
    --ron

  8. #8
    Ron Rosenfeld
    Guest

    Re: How do I find address of cell containing maximum value

    On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <Doug@discussions.microsoft.com>
    wrote:

    >I'd like to have the cell address returned along with a value when I use the
    >MAX function. Is there a way to do that?


    Due to Harlan's critique, I found some other issues with my recommendation.

    So, being lazy, I would just use a VBA routine to accomplish the task, if you
    need it for a 2D reference.

    To enter this UDF, <alt><F11> opens the VB Editor.

    Ensure your project is highlighted in the Project Explorer window, then
    Insert/Module (from the main menu bar on top) and paste the code below into the
    window that opens.

    To use the function, enter =MaxAdr(rng) in some cell where "rng" is the range
    you wish to search.

    As written, it will return the address of the first MAX number it encounters.
    If you want multiple addresses returned, that would be a simple modification,
    depending on how you wanted the addresses returned (comma separated in the same
    cell, or as an array).

    =========================
    Function MaxAdr(rng As Range) As String
    Dim c As Range
    Dim MaxNum As Double

    MaxNum = Application.WorksheetFunction.Max(rng)

    For Each c In rng
    If c.Value = MaxNum Then
    MaxAdr = c.Address
    Exit Function
    End If
    Next c
    End Function
    =======================

    For example, the following will return an array with ALL of the addresses
    containing the MAX number in the range:

    =========================
    Function MaxAdr(rng As Range)
    Dim c As Range
    Dim MaxNum As Double
    Dim Temp()
    Dim d As Long

    MaxNum = Application.WorksheetFunction.Max(rng)

    For Each c In rng
    If c.Value = MaxNum Then
    ReDim Preserve Temp(d)
    Temp(d) = c.Address
    d = d + 1
    End If
    Next c
    MaxAdr = Temp
    End Function
    ========================


    --ron

  9. #9
    Alan Beban
    Guest

    Re: How do I find address of cell containing maximum value

    Doug wrote:
    > I'd like to have the cell address returned along with a value when I use the
    > MAX function. Is there a way to do that?


    If the functions in the freely downloadable file at
    http://home.pacbell.net/beban are available to your workbook

    =MAX($A$1:$C$4)&"
    "&INDEX(ArrayMatch(MAX($A$1:$C$4),$A$1:$C$4,"A",2),ROW(A2))

    filled down to accommodate the number of occurrences of the maximum value.

    Alan Beban

  10. #10
    Harlan Grove
    Guest

    Re: How do I find address of cell containing maximum value

    Ron Rosenfeld wrote...
    ....
    >So, being lazy, I would just use a VBA routine to accomplish the task, if you
    >need it for a 2D reference.

    ....

    VBA unnecessary.

    To return the topmost match in rng,

    =CELL("Address",INDEX(rng,MATCH(TRUE,COUNTIF(OFFSET(rng,ROW(rng)
    -CELL("Row",rng),0,1,),MAX(rng))>0,0),MATCH(MAX(rng),INDEX(rng,MATCH(TRUE,
    COUNTIF(OFFSET(rng,ROW(rng)-CELL("Row",rng),0,1,),MAX(rng))>0,0),0),0)))

    To return the leftmost match in rng,

    =CELL("Address",INDEX(rng,MATCH(MAX(rng),INDEX(rng,0,MATCH(TRUE,
    COUNTIF(OFFSET(rng,0,COLUMN(rng)-CELL("Col",rng),,1),MAX(rng))>0,0)),0),
    MATCH(TRUE,COUNTIF(OFFSET(rng,0,COLUMN(rng)-CELL("Col",rng),,1),MAX(rng))>0,0)))

    Both are array formulas. I will admit that if the final result is a
    text address, then ADDRESS does give shorter formulas.

    Topmost:
    =ADDRESS(INT(MIN(IF(rng=MAX(rng),ROW(rng)*1000+COLUMN(rng)))/1000),
    MOD(MIN(IF(rng=MAX(rng),ROW(rng)*1000+COLUMN(rng))),1000),4)

    Leftmost:
    =ADDRESS(MOD(MIN(IF(rng=MAX(rng),ROW(rng)+100000*COLUMN(rng))),100000),
    INT(MIN(IF(rng=MAX(rng),ROW(rng)+100000*COLUMN(rng)))/100000),4)

    Both array formulas.


  11. #11
    Ron Rosenfeld
    Guest

    Re: How do I find address of cell containing maximum value

    On 30 Aug 2005 08:58:16 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:

    >VBA unnecessary.


    I didn't write it was necessary -- just that I was lazy :-)).

    And the 2nd UDF will return the addresses of all the Max matches.


    --ron

  12. #12
    Registered User
    Join Date
    04-14-2008
    Posts
    2
    Ron,

    I am using your code for returning the address of the maximum value in the range and it works great, thanks

    I was trying to get the second bit of code to work to return the address of all the maximum values in a range but I'm having a bit of difficulty figuring out how it works.

    I've replaced the UDF with the second bit of code, but the cell still only returns one address. What should happen when there are two or more maximum values in the range?

    Many thanks
    Scott.

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674
    Hello Scott,

    Ron's post is a few year's old and posted when these forums had a link with the Microsoft Newsgroups (which they no longer do). I don't believe Ron's a member here so if you want a reply specifically from him you may need to go there.........or perhaps start a new thread in the Excel Programming forum, linking to this thread if necessary, or just posting your code

  14. #14
    Registered User
    Join Date
    04-14-2008
    Posts
    2
    Thanks for the update. As it was Ron's code I was using, I will try to locate him on the Microsoft Newsgroups and if I can't get a reply I will start a new thread.

    Scott.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1