How to find max cell location
 

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

How to find max cell location
 

On Mar 5, 3:09*pm, Opal wrote:
I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Just a little more information...perhaps there is an easier way
to get to what I want. I have a pivot table that shows downtime
for equipment in the shop. I have it set up to show only the top
3 incidents per week. I need to create a chart from the data
and my boss does not want a pivot chart. In the chart I need
to show not only the data, but the column heading. When
a breakdown occurs, our maintenance group classifies the
problem among several different reason codes which vary by
piece of equipment. These are the column headings. I
thought that if I could put in a formula that tells me the
location of the maximum value in the table I could then
use that info to point me to the correct column heading
to reference on the chart.....?? :-S Any advice would
be appreciated.

How to find max cell location
 

On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?


Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

How to find max cell location
 

Try

=ADDRESS(9,MATCH(MAX(B9:AK9),B9:AK9)+1)

Mike

"Opal" wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

How to find max cell location
 

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

How to find max cell location
 

On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps / Lars-Åke

How to find max cell location
 

On Mar 5, 4:18*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:





On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps */ Lars-Åke- Hide quoted text -

- Show quoted text -

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people. So I have a part number in row 5
and reason codes in part 6. Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....

How to find max cell location
 

On Mar 5, 4:51*pm, Opal wrote:
On Mar 5, 4:18*pm, Lars-Åke Aspelin wrote:





On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps */ Lars-Åke- Hide quoted text -

- Show quoted text -

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people. *So I have a part number in row 5
and reason codes in part 6. *Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....- Hide quoted text -

- Show quoted text -

That's okay....got a work around recording a macro that copies, pastes
(transposes), deletes
blanks and copies to the cells where I need it.... Thanks for all
your help!