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.

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?

Harlan Grove wrote:
> "Ron Rosenfeld" <[email protected]> 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

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

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> 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

"Ron Rosenfeld" <[email protected]> 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.

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> 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

Harlan Grove wrote:
> "Ron Rosenfeld" <[email protected]> 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

"Ron Rosenfeld" <[email protected]> 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.

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

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?

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.

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" <[email protected]>
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(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.

On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> 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

Harlan Grove wrote:
> "Ron Rosenfeld" <[email protected]> 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

"Ron Rosenfeld" <[email protected]> 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.

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

Harlan Grove wrote:
> "Ron Rosenfeld" <[email protected]> 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" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> 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

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.

"Ron Rosenfeld" <[email protected]> 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.

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

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

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?

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?

Harlan Grove wrote:
> "Ron Rosenfeld" <[email protected]> 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

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.

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?

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

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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" <[email protected]> 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.

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <[email protected]>
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

On Mon, 29 Aug 2005 21:12:45 -0700, "Harlan Grove" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> 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