Using Lookup and Reference functions with named ranges

Note: Edited to add row & column references to make table easier to read
         Edited to remove ambiguity about manual calculations

Hello Experts,

I'm trying to use Lookup and Reference functions to dynamically define the ranges used by various statistical calculations like AVERAGE, STDEV, CORREL.

The approach i have in mind is to use a a user defined functions for each calculation, using SecurityA, SecurityB, #daysHistory as required parameters.

My workbook has two sheets:

PairList! (note: this sheet might have up to 700 rows)

user input---------------------------------   Calculated values-------------------------------------
SecurityA     SecurityB    #DaysHistory    fnRatioAvg      fnRatioStdDev             fnCorrelation
1 HK            20 HK              5                HELP!           i'll do myself later      i'll do myself later
4 HK            20 HK              5                HELP!            i'll do myself later     i'll do myself later
...
...


Price! (note: this sheet might have up to 65000 rows and 700 securities)
I have currently sorted it ASCENDING, but could change to DESCENDING if the lookups are more efficient.
      A                      B       C
1      SecurityID                      Date      Price
2      1 HK Equity      8/10/2006      84.85
3      1 HK Equity      8/11/2006      84.9
4      1 HK Equity      8/14/2006      85.25
5      1 HK Equity      8/15/2006      85.6
6      1 HK Equity      8/16/2006      86.85
7      1 HK Equity      8/17/2006      87.85
8      1 HK Equity      8/18/2006      88.25
9      1 HK Equity      8/21/2006      87.05
10       20 HK Equity       8/10/2006      13.48
11       20 HK Equity       8/11/2006      13.62
12       20 HK Equity       8/14/2006      13.52
13       20 HK Equity       8/15/2006      13.5
14       20 HK Equity       8/16/2006      13.74
15       20 HK Equity       8/17/2006      13.62
16       20 HK Equity       8/18/2006      13.6
17       20 HK Equity       8/21/2006      13.5
18      4 HK Equity      8/10/2006      28.8
19      4 HK Equity      8/11/2006      28.7
20      4 HK Equity      8/14/2006      28.55
21      4 HK Equity      8/15/2006      28.5
22      4 HK Equity      8/16/2006      28.8
23      4 HK Equity      8/17/2006      28.8
24      4 HK Equity      8/18/2006      28.65
25      4 HK Equity      8/21/2006      28.35
...
...
Assuming data for both Prices! and PairList start in cell A1 (with headers), manually the calculations for the first pair (1 HK / 20 HK) would be
={AVERAGE(Prices!C7:C9/Prices!C15:C17)}
={STDEV(Prices!C7:C9/Prices!C15:C17)}
={CORREL(LN(Prices!C7:C9/Prices!C6:C8),LN(Prices!C15:C17/Prices!C14:C16))}

The result table looks like this:
SecurityA                      SecurityB            #DaysHistory      fnRatioAverage      fnRatioStdDev      fnCorrelation
1 HK Equity      20 HK Equity      3      6.4624                      0.0230                       7%
4 HK Equity      20 HK Equity      3      2.1071                      0.0073                      -18%

The problem I have is defining the ranges dynamically with reference to SecurityA, SecurityB, #daysHistory in the pair table.
I'm experimenting with various Lookup and Reference functions (eg VLOOKUP, COLUMN, ROW, INDEX, INDIRECT, OFFSET, MATCH).

If someone could help me with fnRatioAverage, I should be able to do fnRatiopStdDev, fnCorrelation on my own.

Any suggestions much appreciated. Thanks.

-Tom








tomfolinsbeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Use this function to get the range of the last three prices for a security:

   OFFSET(Price!C2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-4,0,3,1)

and this to find the same but offset by -1 row:

   OFFSET(Price!C2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-5,0,3,1)

where Security is the reference to the security you are looking up.

Kevin
zorvek (Kevin Jones)ConsultantCommented:
So your formula for cell PairList!D2 would be:

   ={AVERAGE(OFFSET(Price!C$2,MATCH(A2,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,A2)-4,0,3,1)/OFFSET(Price!C$2,MATCH(B2,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,B2)-4,0,3,1))}

Note that I made one tweak to the two function templates...

Use this function to get the range of the last three prices for a security:

   OFFSET(Price!C$2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-4,0,3,1)

and this to find the same but offset by -1 row:

   OFFSET(Price!C$2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-5,0,3,1)

Kevin
tomfolinsbeeAuthor Commented:
Thanks Kevin!

I used your function templates for the other two calcs, got the same results with the sample data.

I've got a question about why you use -4 or -5 in the 2nd  (row) parameter of OFFSET? I see how it fits the sample data(ie, 8 prices for each security minus the 3 days of history =5.  But what if the number of days of history to retrieve is 2 or 4 or if the total number of price records for each security is increased?  

I haven't tested yet with a large volume of data, will try that now.

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

zorvek (Kevin Jones)ConsultantCommented:
>I've got a question about why you use -4 or -5 in the 2nd  (row) parameter of OFFSET? I see how it fits the sample data(ie, 8 prices for each security minus the 3 days of history =5.
Not quite. Four is derived by taking the offset to the first entry and adding the number of entries then moving back up the to capture the last three prices. The offset is already too high by one since we used the MATCH function to find it (MATCH returns the row number not the offset). So we have to take away the one AND the three prices to sample which equals four. The five is just one more higher then four.

The templates assumed exactly three prices. If you want to vary that then you will need to make two adjustments in each template...below are revised templates illustrating how to set the number of prices.

Use this function to get the range of the last three prices for a security:

   OFFSET(Price!C$2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-1-NumberPrices,0,NumberPrices,1)

and this to find the same but offset by -1 row:

   OFFSET(Price!C$2,MATCH(Security,Price!A$2:A$65536,0)+COUNTIF(Price!A$2:A$65536,Security)-2-NumberPrices,0,NumberPrices,1)

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patrickabCommented:
Kevin - You're doing it again! - Zzzz - Patrick
tomfolinsbeeAuthor Commented:
You guys saved my bacon on this project, THANKS!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.