Changing the number of cells calculated

**zealot** · 11-09-2006, 07:27 AM

Hello Friends!!

I want to ask if it is possible to make the CORREL function to calculate custom periods of data. For example CORREL(E52:E181,F52:F181) calculates correlation for 129 rows of data (181-52). Do you think that the range that the function takes into account can be instead represented as the number of the current cell minus a cell N rows above (e.g. CORREL(current cell’s address in column E – N rows:current cell’s address in column E, current cell’s address in column F – N rows:current cell’s address in column F)) and the N will be set in a separate cell? For example for N=10 the function will look as follows CORREL(E171:E181,F171:F181) and CORREL(E151:E181,F151:F181) when the N is set to 30.

Hoping for an answer))

Dima

**Carim** · 11-09-2006, 07:30 AM

Hi,

have you tried the offset() function ...

=offset(cell,offsetrows,offsetcolumns)

HTH
Carim

**zealot** · 11-09-2006, 07:40 AM

thansk for a quick answer....as far as I understand the offset function allows to specify the period of data which the function will take into account? I never used it with correl function....do you tink you can give me an example how I can use offset with correl?

thanks a lot!!!

**Mark@Work** · 11-09-2006, 01:33 PM

To simply answer your request for an example:

=CORREL(OFFSET(E181,-N,0,N,1),OFFSET(E181,-N,1,N,1))

Where N is the number of rows you want in your range of cells.

However I would recommend that you check what I have done against the excel help page on the offset function, because it is not entirely straight forward and could cause problems if used blindly.

For example note that the E181 is the last cell in your range, and I ask for an offset of minus N rows from that cell for the start of the range. Also the second OFFSET asks for an offset of one column from the first range. Lastly the ranges are N rows high by 1 column wide.

Mark.

**zealot** · 11-09-2006, 05:01 PM

thanks.!!! i have been able to make the offset function work with correl function....cool!!!now i need to find a way to rotate the N (number of periods considered by the Offset function) from 5 to 100 and tabulate the results in each case...do you think you can guide me in how this can be done?

thanks!!!!!

**Mark@Work** · 11-10-2006, 06:55 AM

In the words of my old teachers,
"If you just copy me blindly you won't learn anything"
I assume that if you are using the corellation function you must be from a scientific background, therefore you realy should be able to work this one out for yourself!

Take some time to look through the entire list of excel functions and see if any can be applied to your problem. [eg ROWS()]

Mark.

**zealot** · 11-10-2006, 07:30 AM

i agree but I am from financial background so these things dont come easy )) I am using solver to rotate the arguments of the offset function with is within my correlation function...the only problem with the SOLVER is that it seems not to do through all possible combinations of the 2 parameters...it justs gives out an answer after a half-second...do you know how to get the solver to check all the variants???

thanks!!

**Mark@Work** · 11-10-2006, 01:31 PM

So are you trying to compare two data sets to try to determine the region within which the correlation is best, or perhaps trying to find the largest region over which the correlation is better than a specified figure?

You probably require an almost full search through the entire solution domain, which is not what solver does. Solver tries to look in the most promising direction having made various assumptions about the problem. The type of assumptions made by solver will be; that the problem can be represented by a continuous function with well defined maxima and minima. In other words not what you have.

Without knowing your problem in detail, I suggest you use the sledgehammer approach.
Devote a full worksheet to calculating EVERY (reasonable) permutation, then chose the one you want.