autocorrelation 1

[electricpete]

Let's say I have a long set of data x[n] for n=0...N

I want an autocorrelation function
R[k] = Sum(x[n]*x[n+k]) / Sum(x[n]^2) where the sum is carried out for k from 0 to M
(M << N)

Can it be done easily in excel (without vba) ?


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[NKT]

Try using the "offset" function for the "n+k" term, and then a "vlookup" function to find the value of R[k] for a given N.

I uploaded a sample spreadsheet here:

http://files.engineering.com/getfil...025a07f995&file=eng-tips_thread770-207621.xls

 

[electricpete]

Thanks, I appreciate that. It looks like I get one k at a time. I would like to plot the function Rhat(k)

http://en.wikipedia.org/wiki/Autocorrelation

I could string out the values of k in columns but that would make for a clumsy spreadsheet.

Is there a clever way to use matrix formula's to do it in place?

If not I'll break down and use vba... should be pretty easy that way.



=====================================
Eng-tips forums: The best place on the web for engineering discussions.