LinkBack URL
About LinkBacksI'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?
Assuming that your entries are in Cells A1:A100, you can do it this way ...Originally Posted by Doug
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
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" <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
"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.
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
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
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
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 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
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.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks