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?

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?

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


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=400281

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?

On Mon, 29 Aug 2005 14:47:23 -0700, "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?

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

"Ron Rosenfeld" 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.

Harlan Grove wrote:
"Ron Rosenfeld" 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

On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" wrote:

"Ron Rosenfeld" 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

On Mon, 29 Aug 2005 14:47:23 -0700, "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?

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

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

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(OFFSE T(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+COL UMN(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*C OLUMN(rng))),100000),
INT(MIN(IF(rng=MAX(rng),ROW(rng)+100000*COLUMN(rng )))/100000),4)

Both array formulas.

On 30 Aug 2005 08:58:16 -0700, "Harlan Grove" 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

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?

The cell formula I have is: {=maxadr(D2:W15)}

Many thanks
Scott.

"Ron Rosenfeld" wrote:

On Mon, 29 Aug 2005 14:47:23 -0700, "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?

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