Need ability to get cell address of max value in range

M

Michael Turnbeaugh

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/com...dg=microsoft.public.excel.worksheet.functions
 
C

Carim

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
 
R

Ragdyer

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 said:
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.
Click to expand...
http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
C

Carim

Oops,

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

Carim
 
M

Michael Turnbeaugh

Thanks a lot. This is exactly what I needed.

Ragdyer said:
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.
Click to expand...
 
R

Ragdyer

Thanks for the feed-back.

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

Biff

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(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A8)>0,0),MATCH(A8,INDEX(tbl,MATCH(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
 
C

Carim

Hi Biff,

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

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

Cheers
Carim
 
M

Michael Turnbeaugh

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