Show Cell Reference
 

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?
--
Thanks for the brainwaves!

Show Cell Reference
 

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!

Show Cell Reference
 

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)

???
--
Thanks for the brainwaves!


"willwonka" wrote:

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!

Show Cell Reference
 

Formula correction:

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

--
Thanks for the brainwaves!


"CJ" wrote:

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)

???
--
Thanks for the brainwaves!


"willwonka" wrote:

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!

Show Cell Reference
 

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.


CJ wrote:
Formula correction:

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

--
Thanks for the brainwaves!


"CJ" wrote:

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)

???
--
Thanks for the brainwaves!


"willwonka" wrote:

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!

Show Cell Reference
 

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)


willwonka wrote:
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.


CJ wrote:
Formula correction:

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

--
Thanks for the brainwaves!


"CJ" wrote:

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)

???
--
Thanks for the brainwaves!


"willwonka" wrote:

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!

Show Cell Reference
 

OK, that works!!

Thanks a bunch!
--
Thanks for the brainwaves!


"willwonka" wrote:

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)


willwonka wrote:
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.


CJ wrote:
Formula correction:

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

--
Thanks for the brainwaves!


"CJ" wrote:

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)

???
--
Thanks for the brainwaves!


"willwonka" wrote:

Maybe this works:

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


CJ wrote:
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?
--
Thanks for the brainwaves!