Have a value be shown as the referneced cell

  • Thread starter Thread starter marc
  • Start date Start date
M

marc

I am running a formula =Max($a$1:$B$55,1) to find the largest number in a set
of data. The answer was found in cell A36.

The formula was fine but I was wondering if there is a formula that will
display the answer's cell location.


The value of A36 is 10.
With my formula =Max($a$1:$B$55,1) the answer is 10.

But I would need another formula to display A36 (or the cell's location)
The find function isn't working
 
One play, in say, C1
=IF(ISNA(MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)),IF(ISNA(MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"","B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0))

The above sequentially matches the result from the MAX down col A first,
then down col B. If there are any ties in the MAX, then only the 1st matched
instance will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
 
ok i think is would work but this was more than I expected. How would it work
if this was my actual data range.

$C$33 : $R$33 and I am finding the MIN
 
Use this instead of my MIN formula:

=CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33"

You can change the 1 in the SMALL function to 2, 3, 4 etc to get the
next smallest etc. If you want to show the cell contents as well as
the reference, you can do this:

=CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33 contains
"&SMALL(C33:R33,1)

which shows:

F33 contains 2

with my test data.

Hope this helps.

Pete
 
Pete what is the +66 and "33" just curious

Pete_UK said:
Use this instead of my MIN formula:

=CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33"

You can change the 1 in the SMALL function to 2, 3, 4 etc to get the
next smallest etc. If you want to show the cell contents as well as
the reference, you can do this:

=CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33 contains
"&SMALL(C33:R33,1)

which shows:

F33 contains 2

with my test data.

Hope this helps.

Pete
Click to expand...
 
because now if I change the data range from C33:R35 instead of R33 it gives
me an answer for N/A?
 
Basically, the formula is returning a text value made up of a letter
and a number (eg F33) which looks like a cell reference. As your data
is all on row 33, then we don't need to search in different rows and
can just return this as a constant value. If you do search in more
than one row, you will need a more complex formula like the one Max
gave to you.

The CHAR function returns the character for the ASCII code provided as
the parameter. MATCH will return the relative postition of the found
cell, so if the found value is in cell C33 then MATCH will return 1.
The ASCII code for C is 67, so 66 needs to be added on in order to
return the correct letter. Note that this approach will only work for
up to column Z.

Hope this helps.

Pete
 
Try this *array* formula:

=ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35)),4)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Don't let the Max functions in the formula confuse you.

This will find the smallest number's address.

If there are duplicates, the last entry is returned.

Blanks are considered as 0's.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


because now if I change the data range from C33:R35 instead of R33 it gives
me an answer for N/A?
 
You can drop the last argument in the Address function (,4).

That just formats the return to a relative reference.
Without it, the return is absolute.

=ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35)))

--

Regards,

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

Try this *array* formula:

=ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35)),4)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Don't let the Max functions in the formula confuse you.

This will find the smallest number's address.

If there are duplicates, the last entry is returned.

Blanks are considered as 0's.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


because now if I change the data range from C33:R35 instead of R33 it gives
me an answer for N/A?
 
RagDyeR came provides an EXCELLENT solution.

I think the following refines his solution just a tad:

First, I name the data range: MyRng.

Second, I used the SMALL function in lieu of the MIN function.

Thus to find the smallest value in the range: MyRng, the following array
function should work:

{=ADDRESS(MAX((MyRng=MIN(MyRng))*ROW(MyRng)),MAX((MyRng=MIN(MyRng))*COLUMN(MyRng)))&" contains the smallest number: "&SMALL(MyRng,1)}

To find the 3rd smallest value, the following function should work:

'{=ADDRESS(MAX((MyRng=SMALL(MyRng,3))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,3))*COLUMN(MyRng)))&" contains the 3rd smallest number: "&SMALL(MyRng,3)}

Thanks marc for a good question and an even greater thank you to RagDyeR for
his solution.

Good Luck.
 
Ooops ... let me re-do my 1st reply. Sorry.

RagDyeR provides an EXCELLENT solution.

I think the following refines his solution just a tad:

First, I name the data range: MyRng.

Second, I used the SMALL function in lieu of the MIN function.

Thus to find the smallest value in the range: MyRng, the following array
function should work:

{=ADDRESS(MAX((MyRng=SMALL(MyRng,1))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,1))*COLUMN(MyRng)))&" contains the smallest number: "&SMALL(MyRng,1)}

To find the 3rd smallest value, the following function should work:

'{=ADDRESS(MAX((MyRng=SMALL(MyRng,3))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,3))*COLUMN(MyRng)))&" contains the 3rd smallest number: "&SMALL(MyRng,3)}

Thanks marc for a good question and an even greater thank you to RagDyeR for
his solution.

Good Luck.
 
Thanks for all of the help everyone!

ND Pard said:
Ooops ... let me re-do my 1st reply. Sorry.

RagDyeR provides an EXCELLENT solution.

I think the following refines his solution just a tad:

First, I name the data range: MyRng.

Second, I used the SMALL function in lieu of the MIN function.

Thus to find the smallest value in the range: MyRng, the following array
function should work:

{=ADDRESS(MAX((MyRng=SMALL(MyRng,1))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,1))*COLUMN(MyRng)))&" contains the smallest number: "&SMALL(MyRng,1)}

To find the 3rd smallest value, the following function should work:

'{=ADDRESS(MAX((MyRng=SMALL(MyRng,3))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,3))*COLUMN(MyRng)))&" contains the 3rd smallest number: "&SMALL(MyRng,3)}

Thanks marc for a good question and an even greater thank you to RagDyeR for
his solution.

Good Luck.
Click to expand...
 
Back
Top