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

1. ## [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. 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

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

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

Hi,

To get address, use the following formula

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

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

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

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

>...
>>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. ## 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
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:

=========================
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)
d = d + 1
End If
Next c
End Function
========================

--ron

9. ## 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?

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. ## 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("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,

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

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

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

Both array formulas.

11. ## 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. 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. 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. 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.

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

#### 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