Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the optimized correlation between 2 sets of data?
Does anyone have any suggestions on how to determine the optimized
correlation between 2 sets of data? There are 2 sets of data under column A & B, and there is a input value in cell C1 Let assume the data set under column A is fixed, and we try to shift forward and backward on the data set under column B. If the input value is 0 in cell C1, then there is no shift movement for the data set on B column. If the input value is 5 in cell C1, then the data set on B column is shift up 5 cells. If the input value is -5 in cell C1, then the data set on B column is shift down 5 cells. As the data set on B column shift upward or downward, the correlation between 2 sets of data changes. My objective is to determine the optimized value in cell C1, therefore, it will return the maximum correlation between 2 sets of data. The range for correlation is the last 250 set of data, and the maximum shift period is 50 set of data under column B. On the other words, I would like to measure the correlation for the last 250 set of data from 51 to 300 under column A & B, and the maximum period to shift forward / backward on column B is 50 sets of data. Does anyone have any suggestions on how to do it? Thank you very much for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the optimized correlation between 2 sets of data?
How are you measuring correlation? Do you have some formula in a cell
somewhere? If so, you could run a solver optimization, to maximize the calculated correlation by changing the input value in C1. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Eric" wrote in message ... Does anyone have any suggestions on how to determine the optimized correlation between 2 sets of data? There are 2 sets of data under column A & B, and there is a input value in cell C1 Let assume the data set under column A is fixed, and we try to shift forward and backward on the data set under column B. If the input value is 0 in cell C1, then there is no shift movement for the data set on B column. If the input value is 5 in cell C1, then the data set on B column is shift up 5 cells. If the input value is -5 in cell C1, then the data set on B column is shift down 5 cells. As the data set on B column shift upward or downward, the correlation between 2 sets of data changes. My objective is to determine the optimized value in cell C1, therefore, it will return the maximum correlation between 2 sets of data. The range for correlation is the last 250 set of data, and the maximum shift period is 50 set of data under column B. On the other words, I would like to measure the correlation for the last 250 set of data from 51 to 300 under column A & B, and the maximum period to shift forward / backward on column B is 50 sets of data. Does anyone have any suggestions on how to do it? Thank you very much for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine correlation for the shifted cycle? | Excel Discussion (Misc queries) | |||
How to determine the correlation between two lists of numbers? | Excel Discussion (Misc queries) | |||
Correlation of data | Excel Discussion (Misc queries) | |||
How do i find a correlation between 2 data point or sets? | Excel Worksheet Functions | |||
How do i determine the operating income for data sets? | New Users to Excel |