> > .. what I would like to do is take a specific column(s) to find the
> > volatilities and correlations of the data points within that range.
> > Is there someway to do
> > =CORREL("column2 of DynamicRange", "column 4 of DynamicRange")
> > or =SQRT(SUMSQ("column2 of DynamicRange")/EDspdvolscorr!$K$3)
> > for example?
>
> Perhaps one way to set it up ..
>
> A sample construct is available at:
>
http://www.savefile.com/files/4522354> Drawing data from specific cols of a dynamic range_Darren_setup.xls
>
> Create 3 defined / named ranges
> (via Insert > Name > Define):
>
> Col_1:
> =OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)
> -1,EDspdvolscorr!$K$4-1,EDspdvolscorr!$K$3)
>
> Col_2:
> =OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)
> -1,EDspdvolscorr!$K$5-1,EDspdvolscorr!$K$3)
>
> Dates:
> =OFFSET(dailychange!$A$2,,,COUNTA(dailychange!$A:$A)-1)
>
> (Dates is a dynamic range for use in the DV below to ease selection of the
> date input)
>
> Then in sheet: EDspdvolscorr,
>
> Assuming the inputs for Date, Days, Column 1, Column 2
> will be made in K2:K5, eg:
>
> Date: 01-Dec-05
> Days: 5
> Column 1: 2
> Column 2: 4
>
> we could put in say, K7:K8
> =CORREL(Col_1,Col_2)
> =SQRT(SUMSQ(Col_2)/$K$3)
> to return the reqd calcs
>
> where K2 contains a DV to select the date input
> (created via Data > Validation, Allow: List, Source: Dates)
> --
> Max
> Singapore
>
http://savefile.com/projects/236895> xdemechanik
> ---
> "Darren" <Darren[ at ]discussions.microsoft.com> wrote in message
> news:2BA8AE46-F3E2-4328-85BF-F68DA123C724[ at ]microsoft.com...
> > Hi,
> >
> > I have historical data; 40 columns of vertically displayed data according
> to
> > date which I use to create volatility and correlation matrices. I would
> like
> > to look at the volatilities and correlations of the 40 contracts for
> > different dates and different time periods. (For example, I would like to
> use
> > data from 2/6/06 to 25 days past that date, or, I would like to use data
> from
> > 12/28/05 to 55 days past that date to find out what the differences were
> for
> > those specific dates and time periods).
> >
> > To do this I have created a dynamic range name using input cells; one for
> > the date, one for the # of days out from that date. The name
> (DynamicRange)
> > refers to:
> >
> =OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)
> -1,1,EDspdvolscorr!$K$3,145)
> >
> > Where EDspdvolscorr!$K$2 is the Desired date to start from and
> > EDspdvolscorr!$K$3 is the desired number of days to go out from.
> >
> > Now what I would like to do is take a specific column(s) to find the
> > volatilities and correlations of the data points within that range. Is
> there
> > someway to do =CORREL("column2 of DynamicRange", "column 4 of
> DynamicRange")
> > or =SQRT(SUMSQ("column2 of DynamicRange")/EDspdvolscorr!$K$3) for example?
> >
> > I think another option I have is to create names for each individual
> column
> > and link them all to the input cells. Which is easier/more efficient?
> >
> > Thanks for your help,
> >
> > -Darren
>
>
>