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
---
"marc" wrote:
> 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

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

"Max" wrote:

> 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
> ---
> "marc" wrote:
> > 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

also where is the answer referenced in the formula.

"Max" wrote:

> 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
> ---
> "marc" wrote:
> > 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

Sorry I am not using MIN OR MAX but rather SMALL

"marc" wrote:

> also where is the answer referenced in the formula.
>
> "Max" wrote:
>
> > 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
> > ---
> > "marc" wrote:
> > > 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

Try this:

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

Hope this helps.

Pete

On Jul 22, 2:32*pm, marc <m...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Max" wrote:
> > 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$5*5,1),$B$1:$B$55,0)),""," B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATC H*(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
> > ---
> > "marc" wrote:
> > > 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- Hide quoted text -
>
> - Show quoted text -

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


On Jul 22, 2:41*pm, marc <m...@discussions.microsoft.com> wrote:
> Sorry I am not using MIN OR MAX but rather SMALL
>
>
>
> "marc" wrote:
> > also where is the answer referenced in the formula.
>
> > "Max" wrote:
>
> > > 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$5*5,1),$B$1:$B$55,0)),""," B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATC H*(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
> > > ---
> > > "marc" wrote:
> > > > 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 thatwill
> > > > 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- Hide quoted text -
>
> - Show quoted text -

Thanks Pete that worked Perfect!!!

"Pete_UK" wrote:

> Try this:
>
> =CHAR(MATCH(MIN(C33:R33),C33:R33,0)+66)&"33"
>
> Hope this helps.
>
> Pete
>
> On Jul 22, 2:32 pm, marc <m...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "Max" wrote:
> > > 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$5Â*5,1),$B$1:$B$55,0)),"", "B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MAT CHÂ*(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
> > > ---
> > > "marc" wrote:
> > > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>

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

"Pete_UK" wrote:

> 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
>
>
> On Jul 22, 2:41 pm, marc <m...@discussions.microsoft.com> wrote:
> > Sorry I am not using MIN OR MAX but rather SMALL
> >
> >
> >
> > "marc" wrote:
> > > also where is the answer referenced in the formula.
> >
> > > "Max" wrote:
> >
> > > > 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$5Â*5,1),$B$1:$B$55,0)),"", "B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MAT CHÂ*(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
> > > > ---
> > > > "marc" wrote:
> > > > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>

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

"Pete_UK" wrote:

> 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
>
>
> On Jul 22, 2:41 pm, marc <m...@discussions.microsoft.com> wrote:
> > Sorry I am not using MIN OR MAX but rather SMALL
> >
> >
> >
> > "marc" wrote:
> > > also where is the answer referenced in the formula.
> >
> > > "Max" wrote:
> >
> > > > 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$5Â*5,1),$B$1:$B$55,0)),"", "B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MAT CHÂ*(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
> > > > ---
> > > > "marc" wrote:
> > > > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>