How do I return the cell address of the largest of a set of values

[Mr. Snrub]

I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the largest value.

=LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
address where that largest value is located?

[papou]

Hello
Try this array formula (validate with Ctrl + Shift + Enter):
=ADDRESS(MIN(IF(B3:Z51=LARGE(B3:Z51,1),ROW(B3:Z51))),MIN(IF(B3:Z51=LARGE(B3:Z51,1),COLUMN(B3:Z51))))

HTH
Cordially
Pascal

"Mr. Snrub" <Mr. Snrub@discussions.microsoft.com> a écrit dans le message de
news: 5ECBDE62-5667-4376-9CCA-A8CE9E40F210@microsoft.com...
>I have a table of integers ranging from cells B3 to Z51, and I want to find
> the cell address of the largest value.
>
> =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the
> cell
> address where that largest value is located?

[Ragdyer]

Just another way:

=ADDRESS(MAX((B3:Z51=MAX(B3:Z51))*ROW(B3:Z51)),MAX((B3:Z51=MAX(B3:Z51))*COLU
MN(B3:Z51)))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--
Regards,

RD

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

"papou" <cestpasbon@çanonplus44.fr> wrote in message
news:OVwl2pqXFHA.3032@TK2MSFTNGP10.phx.gbl...
> Hello
> Try this array formula (validate with Ctrl + Shift + Enter):
>
=ADDRESS(MIN(IF(B3:Z51=LARGE(B3:Z51,1),ROW(B3:Z51))),MIN(IF(B3:Z51=LARGE(B3:
Z51,1),COLUMN(B3:Z51))))
>
> HTH
> Cordially
> Pascal
>
> "Mr. Snrub" <Mr. Snrub@discussions.microsoft.com> a écrit dans le message
de
> news: 5ECBDE62-5667-4376-9CCA-A8CE9E40F210@microsoft.com...
> >I have a table of integers ranging from cells B3 to Z51, and I want to
find
> > the cell address of the largest value.
> >
> > =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the
> > cell
> > address where that largest value is located?
>
>

[papou]

Hi
I would also recommend to Mr Snrub the MAX function because of Aladin's
judicious remark.

Cordially
Pascal

"Ragdyer" <RagDyer@cutoutmsn.com> a écrit dans le message de news:
%23DQS9NsXFHA.3464@TK2MSFTNGP10.phx.gbl...
> Just another way:
>
> =ADDRESS(MAX((B3:Z51=MAX(B3:Z51))*ROW(B3:Z51)),MAX((B3:Z51=MAX(B3:Z51))*COLU
> MN(B3:Z51)))
> --
> Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
> of
> the regular <Enter>, which will *automatically* enclose the formula in
> curly
> brackets, which *cannot* be done manually.
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "papou" <cestpasbon@çanonplus44.fr> wrote in message
> news:OVwl2pqXFHA.3032@TK2MSFTNGP10.phx.gbl...
>> Hello
>> Try this array formula (validate with Ctrl + Shift + Enter):
>>
> =ADDRESS(MIN(IF(B3:Z51=LARGE(B3:Z51,1),ROW(B3:Z51))),MIN(IF(B3:Z51=LARGE(B3:
> Z51,1),COLUMN(B3:Z51))))
>>
>> HTH
>> Cordially
>> Pascal
>>
>> "Mr. Snrub" <Mr. Snrub@discussions.microsoft.com> a écrit dans le message
> de
>> news: 5ECBDE62-5667-4376-9CCA-A8CE9E40F210@microsoft.com...
>> >I have a table of integers ranging from cells B3 to Z51, and I want to
> find
>> > the cell address of the largest value.
>> >
>> > =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the
>> > cell
>> > address where that largest value is located?
>>
>>
>

[Aladin Akyurek]

Mr. Snrub wrote:
> I have a table of integers ranging from cells B3 to Z51, and I want to find
> the cell address of the largest value.
>
> =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
> address where that largest value is located?

Be aware of the fact that there can be multiple instances of the largest
value. See:

http://tinyurl.com/dph4d

[Mr. Snrub]

Yes, when there is the exact same value in two different cells, I want to be
able to find both cell addresses.

For example, LARGE(B3:Z51, 3) will find the third-largest value, and
LARGE(B3:Z51, 4) will find the fourth-largest value. If those two values are
the same, how do I find their corresponding addresses? papau's formula don't
seem to work in this case (it repeats the first cell address found when there
is a "tie"), and (as far as I know) MAX only finds the largest value, not the
second-, third- or fourth-largest.

Thanks for all your help, by the way!

Mr. Snrub

"Aladin Akyurek" wrote:

> Mr. Snrub wrote:
> > I have a table of integers ranging from cells B3 to Z51, and I want to find
> > the cell address of the largest value.
> >
> > =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
> > address where that largest value is located?
>
> Be aware of the fact that there can be multiple instances of the largest
> value. See:
>
> http://tinyurl.com/dph4d
>

[Aladin Akyurek]

Mr. Snrub wrote:
> Yes, when there is the exact same value in two different cells, I want to be
> able to find both cell addresses.
>
> For example, LARGE(B3:Z51, 3) will find the third-largest value, and
> LARGE(B3:Z51, 4) will find the fourth-largest value. If those two values are
> the same, how do I find their corresponding addresses? papau's formula don't
> seem to work in this case (it repeats the first cell address found when there
> is a "tie"), and (as far as I know) MAX only finds the largest value, not the
> second-, third- or fourth-largest.
>
> Thanks for all your help, by the way!
>
> Mr. Snrub
>
> "Aladin Akyurek" wrote:
>
>
>>Mr. Snrub wrote:
>>
>>>I have a table of integers ranging from cells B3 to Z51, and I want to find
>>>the cell address of the largest value.
>>>
>>>=LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
>>>address where that largest value is located?
>>
>>Be aware of the fact that there can be multiple instances of the largest
>>value. See:
>>
>>http://tinyurl.com/dph4d
>>

The link I quoted describes a formula system of mine that is capable of
returning cell addresses of all max/largest value instances.

[Mr. Snrub]

I tried doing that little example, but I only got $A$2, not $A$2 and $A$6.

Mr. Snrub

"Aladin Akyurek" wrote:

> The link I quoted describes a formula system of mine that is capable of
> returning cell addresses of all max/largest value instances.
>

[Martin P]

People talk of helper columns, but I have a suggestion with a few helper
worksheets. For convenience the worksheets have been given names. A few
ranges have also been given names:
blok list!$B$3:$Z$51
columnnumbers columnnumbers!$B$3:$Z$51
rownumbers rownumbers!$B$3:$Z$51
unique unique!$B$3:$Z$51
uniquerank 'unique ranks'!$B$3:$Z$51
List
Contains the values in cells B3 to Z51. A3 to A51 are numbered from 1
upwards, and also B2 to Z2.
Ranks
Cell B3: =RANK(list!B3,blok) (copied to B3:Z51)
Unique
Cell B3: =RANK(list!B3,blok) (copied to B3:Z51)
Unique ranks
Cell B3: =RANK(unique!B3,unique) (copied to B3:Z51
Rownumbers
Cell B3: =ROW(A3) (copied to B3:Z51)
Columnnumbers
Cell B3: =COLUMN(A3)
Sheet8
From B1 to F1 the column headings are Row, Column, Value, Unique value, Rank
In column A from cell A2 the cells are numbered from 1 upwards.
Cell B2: =SUMPRODUCT(rownumbers,--(uniquerank=$A2))
Cell C2: =SUMPRODUCT(columnnumbers,--(uniquerank=$A2))
Cell D2: =SUMPRODUCT(blok,--(uniquerank=$A2))
Cell E2: =SUMPRODUCT(unique,--(uniquerank=A2))
Cell F2: =SUMPRODUCT(ranks!$B$3:$Z$51,--(uniquerank=$A2))
Copy the cells down as far as is necessary.

"Mr. Snrub" wrote:

> I have a table of integers ranging from cells B3 to Z51, and I want to find
> the cell address of the largest value.
>
> =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
> address where that largest value is located?