How to determine the optimized correlation between 2 sets of data?

[Eric]

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

 

[Jon Peltier]

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