LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
automandc
 
Posts: n/a
Default Retrieving a Reference

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Absolute Worksheet reference number Tony M Excel Discussion (Misc queries) 4 March 21st 05 06:10 PM
how to create a variable column in cell reference Sampson Excel Worksheet Functions 3 February 21st 05 10:13 PM
Cell Reference Math Ralph Howarth Excel Worksheet Functions 0 January 26th 05 06:27 PM
How do I reference multiple rows Awetronics Excel Worksheet Functions 1 November 4th 04 12:37 AM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"