## Autocorrelation in Excel

I have a set of 9,999 equi-spaced data points, and I would like to calculate the autocorrelations for ALL lags up to 5,000.

In Excel, I want to have this code:

=CORREL(B1:B9998,B2:B9999)
=CORREL(B1:B9997,B3:B9999)
=CORREL(B1:B9996,B4:B9999)
...
=CORREL(B1:B5000,B5000:B9999)

Unfortunately, the formula does not "translate" nicely when I extend it downwards.

Also I have Excel 2008 for Mac, which does not handle VB Macros.

Can anyone direct me? Or suggest a better way of doing it? I don't have MatLab, but I do have Mathematica.

 PhysOrg.com science news on PhysOrg.com >> Ants and carnivorous plants conspire for mutualistic feeding>> Forecast for Titan: Wild weather could be ahead>> Researchers stitch defects into the world's thinnest semiconductor
 Well, I am going to suggest Python, but only because you thought of Matlab, in the first place...which shows that you might be inclined to some scripting. Actually, there is FreeMat which is an a-la-matlab application. But also, there is Python with its numpy and scipy (scipy.stats) modules you can do anything you want and plot it right away with matplotlib. ...just a thought
 Recognitions: Homework Help Try: =CORREL(OFFSET($B$1, 0, 0, 9999-A1, 1 ),OFFSET($B$1, A1, 0, 9999-A1, 1 )) =CORREL(OFFSET($B$1, 0, 0, 9999-A2, 1 ),OFFSET($B$1, A2, 0, 9999-A2, 1 )) after you have filled column A with the consecutive row numbers (1, 2, 3, ...)

## Autocorrelation in Excel

Gsal, sorry, I don't know Python.

The above-posted formula works great though!