Retrieving a Reference

[Guest]

Is there a way to retrieve the reference to a Statistical/Math function
result, rather than the actual result?

I have a large table (R1150 X C50) with numeric values in each cell. I want
to put all of the data into numerical order, but retain the name of the
column each data point came from.

Let's say the table is at A1:Z1000. I can use the LARGE function to choose
the nth largest value with LARGE($A$1:$Z$1000,n). Make "n" a reference to a
series in an adjoining column, and it is easy to create a one-dimensional
list of the top n values. However, there is no way to associate each value
back to the table for the purpose of finding the column it came from.
Neither VLOOKUP nor HLOOKUP will work, since the value could come from any
column or row in the 2D table.

It seems that if LARGE goes out there and evaluates all of the datapoints to
determine the nth largest, it ought to be able to tell me where it got that
datapoint from.

So, is there a way to extract the reference rather than the result from a
function like "LARGE"? (this would also apply to any function that singles
out a specific value from an array, such as "MIN", "MAX"; but not calculation
functions like "AVERAGE").

 

[Peo Sjoblom]

Assume you have a table A1:E10 and you want the 2nd largest value

=LARGE($A$1:$E$10,2)

this formula will return the cell reference that holds the 2nd largest value


=CELL("address",INDEX($A$1:$E$10,MIN(IF($A$1:$E$10=LARGE($A$1:$E$10,2),ROW($A$1:$E$10))),MIN(IF($A$1:$E$10=LARGE($A$1:$E$10,2),COLUMN($A$1:$E$10)))))

it needs to be entered with

ctrl + shift & enter

note that if there are multiple 2nd largest values it will return the first
occurrence counted from
A1

also note that if you use for instance a table like B3:F20 then the index
part should be

INDEX(A1:F20 or else you have to offset the rows/columns since ROW and
COLUMN always will start from first row/column

 

[David McRitchie]

See use of MATCH Worksheet Function in
http://www.mvps.org/dmcritchie/excel/vlookup.htm#match

 

[Harlan Grove]

I have a large table (R1150 X C50) with numeric values in each cell. I want
to put all of the data into numerical order, but retain the name of the
column each data point came from.

....

50 * 1150 = 57500, so not a problem fitting all observations into separate
rows in one worksheet.

Simplest way I can think of is naming your original data range Data, then
extracting it into 2 colums (original column letter in first column, data
value in sedond column), coverting formulas to values, then sorting the
result.

If the top-left cell of the extract range were X5, enter the following
formulas.

X5:
=SUBSTITUTE(ADDRESS(1,1+INT((ROW()-ROW($X$5))/ROWS(Data)),4),"1","")

Y5:
=INDEX(Data,1+MOD(ROW()-ROW($X$5),ROWS(Data)),
1+INT((ROW()-ROW($X$5))/ROWS(Data)))

Fill X5:Y5 down into X6:Y57504. Select X5:Y57504, Edit > Copy, Edit > Paste
Special as values, Data > Sort on column Y in descending order. You should
have the data values in col Y sorted in descending order and their original
column letters in the same row in col X.

 

[Guest]

Thanks to all who responded so far. Each of your proposed solutions offers
promise, and I will use them to build a final solution.

Peo's solution is the most on-point (actually finding the address of the
resulting cell), but it is so bulky that I fear it will seriously choke in a
sheet with 50,000+ instances of the formula. He also points up the problem
with using "LARGE": failure to properly handle identical values.

The info on David's page is great, and will help me solve a lot of problems
I run across when tinkering in Excel.

Harlan's solution is perhaps less elegant (no offense), but it is what I am
currently doing -- only I've been doing it manually with cut-and-paste to put
the data into one column. (First I create a second set of columns that
contain a string of the data point cat'ed with the header description, then
seperate them back into two separate columns after combining into a single
column list). Harlan's method will allow me to programatically create the
unified list I am currently doing manually and skip the step of
double-conversion. Of course, it has the drawback of quickly running up
against the 65K row limit for tables that get big.

It would ultimately be nice if you could do something like this:

=Large&(data_range,n)

and get a pointer to the result (e.g. a reference), instead of having word
actually resolve the result. This would be kind of like a reverse-INDIRECT
function.

Sometimes I really wish there was a way to do loops directly in the
worksheets (like a FOR..NEXT loop), and have it fill in a range with the
results.

Anyway, thanks again everyone -- the MSDN forums always come through for me.

Tobias