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

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?

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. a écrit dans le message de
news: ...
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?

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

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" wrote in message
...
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. a écrit dans le message
de
news: ...
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?

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

Cordially
Pascal

"Ragdyer" a écrit dans le message de news:
...
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" wrote in message
...
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. a écrit dans le message
de
news: ...
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?

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

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.

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.

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?