When looking at a range of cells, I need both the contents of the maximum
value in the range and the actual cell address of that cell. For example, I
have a series of readings from A1 to Y31 to represent every hour and day of a
month. If the highest reading is in the 11th hour of the 15th day (obtained
by the function MAX(A1:Z31) then i need to be able to get the actual address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Hi Michael.

With an array formula

{=ADDRESS(MAX(IF(A1:Y31=MAX(A1:Y31),ROW(A1:Y31)," ")),COLUMN(A1:Y31))}


Use Control Shift Enter to enter the formula ...

HTH
Cheers
Carim

Try this *array* formula:

=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Turnbeaugh" <Michael wrote in
message ...
When looking at a range of cells, I need both the contents of the maximum
value in the range and the actual cell address of that cell. For example,
I
have a series of readings from A1 to Y31 to represent every hour and day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...et.f unctions

Oops,

Sorry for my mistake, use Ragdyer 's array formula ...
it is the right one ...

Carim

What happens when there are duplicate Max's or Min's?

<g

Biff

"Ragdyer" wrote in message
...
Try this *array* formula:

=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Turnbeaugh" <Michael wrote
in
message ...
When looking at a range of cells, I need both the contents of the maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...et.f unctions

It should give you the last one ... no?<g

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
What happens when there are duplicate Max's or Min's?

<g

Biff

"Ragdyer" wrote in message
...
Try this *array* formula:


=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Michael Turnbeaugh" <Michael
wrote
in
message ...
When looking at a range of cells, I need both the contents of the
maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and
day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...et.f unctions

http://img45.imageshack.us/img45/1046/maxaddressgd6.jpg

Harlan "schooled" me on this once. Basically, you have to decide which
direction has precedence:

left to right, top to bottom or top to bottom, left to right.

I think I have a formula (Harlan's formula) somewhere that does this. I'll
see if I can find it.

Biff

"Ragdyer" wrote in message
...
It should give you the last one ... no?<g

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
What happens when there are duplicate Max's or Min's?

<g

Biff

"Ragdyer" wrote in message
...
Try this *array* formula:


=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Michael Turnbeaugh" <Michael
wrote
in
message ...
When looking at a range of cells, I need both the contents of the
maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and
day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...et.f unctions

Thanks a lot. This is exactly what I needed.

"Ragdyer" wrote:

Try this *array* formula:

=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Turnbeaugh" <Michael wrote in
message ...
When looking at a range of cells, I need both the contents of the maximum
value in the range and the actual cell address of that cell. For example,
I
have a series of readings from A1 to Y31 to represent every hour and day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...et.f unctions

Thanks for the feed-back.

And do take note ... if it's at all important to you ... Biff's concern
about duplicates.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Turnbeaugh" wrote in
message ...
Thanks a lot. This is exactly what I needed.

"Ragdyer" wrote:

Try this *array* formula:


=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Michael Turnbeaugh" <Michael
wrote in
message ...
When looking at a range of cells, I need both the contents of the
maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and
day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click
the "I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...et.f unctions

Here's that formula.

tbl = named range
A8 = lookup value (you could replace that with MAX(tbl) )

Array entered:

=CELL("Address",INDEX(tbl,MATCH(TRUE,COUNTIF(OFFSE T(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A8)0,0),MATCH(A8,INDEX(tbl,M ATCH(TRUE,COUNTIF(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A8)0,0),0),0)))

This works by row then column or top to bottom, left to right.

This leads to another mind numbing question. What if you wanted *all* the
adresses that meet the criteria? If the range was a one dimensional array it
would be no problem. But for a multi dimensional array........

Biff

"Biff" wrote in message
...
http://img45.imageshack.us/img45/1046/maxaddressgd6.jpg

Harlan "schooled" me on this once. Basically, you have to decide which
direction has precedence:

left to right, top to bottom or top to bottom, left to right.

I think I have a formula (Harlan's formula) somewhere that does this. I'll
see if I can find it.

Biff

"Ragdyer" wrote in message
...
It should give you the last one ... no?<g

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
What happens when there are duplicate Max's or Min's?

<g

Biff

"Ragdyer" wrote in message
...
Try this *array* formula:


=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"Michael Turnbeaugh" <Michael
wrote
in
message ...
When looking at a range of cells, I need both the contents of the
maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and
day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...et.f unctions

Hi Biff,

Thanks a lot for your insight and for sharing your formula ...

It 's always great to keep on learning ...!!!

Cheers
Carim

Actually Biff's first question is a concern as I would really like to return
the first occurrence of the duplicate Max or Min, if any. I'm trying to
digest his method for working top-to-bottom and right-to-left which would
solve the further condition that I was looking for.

I definitely appreciate all the assistance on this question.

"Ragdyer" wrote:

Thanks for the feed-back.

And do take note ... if it's at all important to you ... Biff's concern
about duplicates.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Turnbeaugh" wrote in
message ...
Thanks a lot. This is exactly what I needed.

"Ragdyer" wrote:

Try this *array* formula:


=ADDRESS(MAX((A1:Y31=MAX(A1:Y31))*ROW(A1:Y31)),MAX ((A1:Y31=MAX(A1:Y31))*COLU
MN(A1:Y31)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Michael Turnbeaugh" <Michael
wrote in
message ...
When looking at a range of cells, I need both the contents of the
maximum
value in the range and the actual cell address of that cell. For
example,
I
have a series of readings from A1 to Y31 to represent every hour and
day
of a
month. If the highest reading is in the 11th hour of the 15th day
(obtained
by the function MAX(A1:Z31) then i need to be able to get the actual
address
of K15 returned also. This is used for billing purposes.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click
the "I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...et.f unctions