Show Cell Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CJ - 18 Oct 2006 19:07 GMT

Hi Groupies

I would like to be able to see which cell the Max function is referring to.

For example: I have a list of values in B1 - B250. The Max function returns
the highest value in that range, but I would also like to find out which cell
contains that value. Is this possible?

Signature

Thanks for the brainwaves!

Reply to this Message

willwonka - 18 Oct 2006 19:25 GMT

Maybe this works:

=address(match(max(b1:b250),b1:b250,0),2)

> Hi Groupies
>
[quoted text clipped - 3 lines]
> the highest value in that range, but I would also like to find out which cell
> contains that value. Is this possible?

Reply to this Message

CJ - 18 Oct 2006 20:02 GMT

Hi, I it is close, but not quite.

I created a column of data starting at 10 incrementing by 5 from D2 - D20
(10 - 100). When I use your suggestion in cell D21, it give me the answer of
$B$19

Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2)

???

Signature

Thanks for the brainwaves!

> Maybe this works:
>
[quoted text clipped - 7 lines]
> > the highest value in that range, but I would also like to find out which cell
> > contains that value. Is this possible?

Reply to this Message

CJ - 18 Oct 2006 20:04 GMT

Formula correction:

=ADDRESS(MATCH(MAX(D2:D20),D2:D20,0),2)

Signature

Thanks for the brainwaves!

> Hi, I it is close, but not quite.
>
[quoted text clipped - 17 lines]
> > > the highest value in that range, but I would also like to find out which cell
> > > contains that value. Is this possible?

Reply to this Message

willwonka - 18 Oct 2006 20:13 GMT

I should have said that the 2 stood for the second column which is
column B. If you are in column D, then a 4 should work.

> Formula correction:
>
[quoted text clipped - 26 lines]
> > > > the highest value in that range, but I would also like to find out which cell
> > > > contains that value. Is this possible?

Reply to this Message

willwonka - 18 Oct 2006 20:15 GMT

Also, the MATCH formula returns the row number within the range, so if
you don't start on row 1 then would need to add the amount of rows
above your range.

=ADDRESS(MATCH(MAX(D2:D20),D2:D20,0)+1,4)

> I should have said that the 2 stood for the second column which is
> column B. If you are in column D, then a 4 should work.
[quoted text clipped - 29 lines]
> > > > > the highest value in that range, but I would also like to find out which cell
> > > > > contains that value. Is this possible?

Reply to this Message

CJ - 18 Oct 2006 21:22 GMT

OK, that works!!

Thanks a bunch!

Signature

Thanks for the brainwaves!

> Also, the MATCH formula returns the row number within the range, so if
> you don't start on row 1 then would need to add the amount of rows
[quoted text clipped - 35 lines]
> > > > > > the highest value in that range, but I would also like to find out which cell
> > > > > > contains that value. Is this possible?

Reply to this Message