Optimising correlation by skewing data

[MCap]

Hi guys,

I'm trying to establish correlation between two columns of data using the CORREL() function. I would like to optimise the correlation by moving the second column of data downward, in other words skewing one data set in order to establish the best fit.

I have attached a dummy spreadsheet; Column A shows the day, Column B shows the first column of data and Column C the second. CORREL: shows the correlation between the two arrays. I would like to achieve the highest value of CORREL, by skewing the data within Column C.

Thanks a lot guys

MCap

[MCap]

I have updated the sheet; using the OFFSET function I have created a way to skew the data, but I can't get a satisfactory answer from Solver. Any help appreciated.

[shg]

Looking at the first workbook, you would want to find the maximum correlation between col B and col C by sliding one against the other?

You realize that in doing so, you would reduce the number of data points used for the correlation?

Would a VBA solution be acceptable?

[MCap]

Yes I think when I have it in place what I will do is chop off 100 values from the top of column B, then slide C downwards from 1 to 100; that way there will always be corresponding values.

A VBA solution would definitely be acceptable, although I think I am fairly close with the second worksheet. Any help at all gladly taken.

[shg]

There are 5464 values. What's the smallest set of values to be tested for correlation?

[MCap]

Ideally I want to optimise correlation between the entire data sets, by skewing Column C by 0<x<101 days.

[MCap]

I've managed to get it done, thanks

[shg]

OK, good job.

I wrote some code that ran a while. When I stopped it, the highest result was =Correl(B12:B4970, C415:C5373) ~ 0.524952. I'll post it if you like.

[MCap]

OK, good job.

I wrote some code that ran a while. When I stopped it, the highest result was =Correl(B12:B4970, C415:C5373) ~ 0.524952. I'll post it if you like.

Yes please I would really appreciate that.

I will attach the work that I've done in a second once the upload feature starts working again, so you can see the direction I went in. If you fiddle with the OFFSET value it will move the column up and down without returning zero values (I hide columns E and F when working with the sheet); I have also managed to dynamically calculate the correlation for just the rows containing data in both columns.

[shg]

Please Login or Register  to view this content.

It writes results to the Immediate window (Ctrl+G to see it if it's not already visible.)

It's hideous to repeatedly calculate Correl in this fashion. I'd expect at least a 10x improvement in performance if the data were read into arrays and Correl was calculated incrementally, but that would take a bit of time to code.

[MCap]

Thanks, that's great. I need to brush up on some VBA!

Attached is the sheet I came up with, with comments in the relevant cells. I was hoping to use Solver to maximise the value of N110 by changing N101... if you think I am on the right lines with what I've done I'd appreciate any input.

Thanks again, you've been very helpful

[shg]

I don't know if that will work; try it.

However, Solver couldn't do anything other than ratchet through the offset values; there is nothing to work its magic dervatives on. It's best at solving smooth functions.