Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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"). |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions | |||
How do I reference multiple rows | Excel Worksheet Functions |