Location of Large

  • Thread starter Thread starter Moni
  • Start date Start date
M

Moni

I'm using the formula: "=LARGE(('FY04'!W2,'FY04'!
T16,'FY04'!N27,'FY04'!N39,'FY04'!Q54,'FY04'!N66,'FY04'!
Q77,'FY04'!K114,'FY04'!H128,'FY04'!T168,'FY04'!
N181,'FY04'!K192,'FY04'!D182),1)" I would like to find
the formula and syntax to locate the "cell of choice" so
that I can add offset to the formula and retrieve
information in column C of that row. Any ideas? I
prefer not to use Macros, but would like to learn the
actual UDF. Thanks.
 
Moni said:
I'm using the formula: "=LARGE(('FY04'!W2,'FY04'!
T16,'FY04'!N27,'FY04'!N39,'FY04'!Q54,'FY04'!N66,'FY04'!
Q77,'FY04'!K114,'FY04'!H128,'FY04'!T168,'FY04'!
N181,'FY04'!K192,'FY04'!D182),1)" I would like to find
the formula and syntax to locate the "cell of choice" so
that I can add offset to the formula and retrieve
information in column C of that row. Any ideas? I
prefer not to use Macros, but would like to learn the
actual UDF. Thanks.
Click to expand...

One possibility involves putting the addresses for these disjoint,
nonadjacent cells into an array of strings, then wrapping that inside
INDIRECT. For example,

=INDEX({"'FY04'!W2","'FY04'!T16","'FY04'!N27","'FY04'!N39","'FY04'!Q54",
"'FY04'!N66","'FY04'!Q77","'FY04'!K114","'FY04'!H128","'FY04'!T168",
"'FY04'!N181","'FY04'!K192","'FY04'!D182"},
MATCH(LARGE(N(INDIRECT({"'FY04'!W2","'FY04'!T16","'FY04'!N27",
"'FY04'!N39","'FY04'!Q54","'FY04'!N66","'FY04'!Q77","'FY04'!K114",
"'FY04'!H128","'FY04'!T168","'FY04'!N181","'FY04'!K192","'FY04'!D182"})),
1),N(INDIRECT({"'FY04'!W2","'FY04'!T16","'FY04'!N27","'FY04'!N39",
"'FY04'!Q54","'FY04'!N66","'FY04'!Q77","'FY04'!K114","'FY04'!H128",
"'FY04'!T168","'FY04'!N181","'FY04'!K192","'FY04'!D182"})),0))

Even better would be to enter these addresses in another, single area range
like X1:X13, then use the formula

=INDEX(X1:X13,MATCH(LARGE(N(INDIRECT(X1:X13)),1),N(INDIRECT(X1:X13)),0))
 
Back
Top