Thanks for feeding back, but see my later post for the embellishment
you asked for.

Pete

On Jul 22, 2:57*pm, marc <m...@discussions.microsoft.com> wrote:
> Thanks Pete that worked Perfect!!!
>

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

On Jul 22, 3:04*pm, marc <m...@discussions.microsoft.com> wrote:
> 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
>

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!
================================================== ===


"marc" <marc@discussions.microsoft.com> wrote in message
news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
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"&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 -
>
>

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 !
-----------------------------------------------------------------------------------------------

"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:eaJ2MiA7IHA.2336@TK2MSFTNGP03.phx.gbl...
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!
================================================== ===


"marc" <marc@discussions.microsoft.com> wrote in message
news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
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"&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 -
>
>

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.

"RagDyeR" wrote:

> 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 !
> -----------------------------------------------------------------------------------------------
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:eaJ2MiA7IHA.2336@TK2MSFTNGP03.phx.gbl...
> 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!
> ================================================== ===
>
>
> "marc" <marc@discussions.microsoft.com> wrote in message
> news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
> 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 -
> >
> >
>
>
>
>

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)),M AX((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.


"RagDyeR" wrote:

> 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 !
> -----------------------------------------------------------------------------------------------
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:eaJ2MiA7IHA.2336@TK2MSFTNGP03.phx.gbl...
> 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!
> ================================================== ===
>
>
> "marc" <marc@discussions.microsoft.com> wrote in message
> news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
> 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 -
> >
> >
>
>
>
>

Thanks for all of the help everyone!

"ND Pard" wrote:

> 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)),M AX((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.
>
>
> "RagDyeR" wrote:
>
> > 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 !
> > -----------------------------------------------------------------------------------------------
> >
> > "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> > news:eaJ2MiA7IHA.2336@TK2MSFTNGP03.phx.gbl...
> > 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!
> > ================================================== ===
> >
> >
> > "marc" <marc@discussions.microsoft.com> wrote in message
> > news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
> > 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 -
> > >
> > >
> >
> >
> >
> >

We appreciate your feed-back.
--

Regards,

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

"marc" <marc@discussions.microsoft.com> wrote in message
news:DF6DD362-0E8B-4B9A-B865-3F6A78D8C292@microsoft.com...
Thanks for all of the help everyone!

"ND Pard" wrote:

> 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)),M AX((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.
>
>
> "RagDyeR" wrote:
>
> > 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 !
> > -----------------------------------------------------------------------------------------------
> >
> > "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> > news:eaJ2MiA7IHA.2336@TK2MSFTNGP03.phx.gbl...
> > 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!
> > ================================================== ===
> >
> >
> > "marc" <marc@discussions.microsoft.com> wrote in message
> > news:FDF10412-3C96-4EB1-9170-83CB27E1BFAD@microsoft.com...
> > 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"&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 -
> > >
> > >
> >
> >
> >
> >