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

--
Regards,

Peo Sjoblom


"automandc" wrote in message
...
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").