Autocorrelation in Excel


by MichalXC
Tags: autocorrelation, correlation, excel
MichalXC
MichalXC is offline
#1
Jun27-11, 03:56 PM
P: 41
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.

Thanks in advance.
Phys.Org News Partner Science news on Phys.org
Simplicity is key to co-operative robots
Chemical vapor deposition used to grow atomic layer materials on top of each other
Earliest ancestor of land herbivores discovered
gsal
gsal is offline
#2
Jun27-11, 07:36 PM
P: 838
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
I like Serena
I like Serena is offline
#3
Jun27-11, 11:51 PM
HW Helper
I like Serena's Avatar
P: 6,189
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, ...)

MichalXC
MichalXC is offline
#4
Jun28-11, 09:43 AM
P: 41

Autocorrelation in Excel


Gsal, sorry, I don't know Python.

The above-posted formula works great though!

Thanks, both, for your replies!
I like Serena
I like Serena is offline
#5
Jun28-11, 10:49 AM
HW Helper
I like Serena's Avatar
P: 6,189
Glad to be of help!
MichalXC
MichalXC is offline
#6
Jun30-11, 06:58 PM
P: 41
Hi again.

Excel (understandably) becomes unresponsive when asked to do some of these calculations. Seeing as much, I would be most grateful if someone could show me how to implement a correlogram (autocorrelation plot) in Mathematica.
Proton Soup
Proton Soup is offline
#7
Jul15-11, 06:40 PM
P: 1,070
since you mentioned matlab (deja vu?), another program you might want to check out is Scilab. not a full clone, but very similar to matlab.
gsal
gsal is offline
#8
Jul15-11, 10:59 PM
P: 838
Then, there is Octave and Freemat. And of course, Python with numpy/scipy.


Register to reply

Related Discussions
Autocorrelation and ESD/PSD Electrical Engineering 2
Autocorrelation and autocorrelation time Set Theory, Logic, Probability, Statistics 3
Autocorrelation WSS RP Electrical Engineering 1
autocorrelation time General Physics 2