We help IT Professionals succeed at work.
Get Started

Using Lookup and Reference functions with named ranges

tomfolinsbee
tomfolinsbee asked
on
914 Views
Last Modified: 2008-02-01
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








Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2008
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE