Autocorrelation in Excel

  1. Jun 27, 2011 #1
    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.
     
  2. jcsd
  3. Jun 27, 2011 #2
    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
     
  4. Jun 27, 2011 #3

    I like Serena

    User Avatar
    Homework Helper

    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, ...)
     
  5. Jun 28, 2011 #4
    Gsal, sorry, I don't know Python.

    The above-posted formula works great though!

    Thanks, both, for your replies!
     
  6. Jun 28, 2011 #5

    I like Serena

    User Avatar
    Homework Helper

    Glad to be of help! :smile:
     
  7. Jun 30, 2011 #6
    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.
     
  8. Jul 15, 2011 #7
    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.
     
  9. Jul 15, 2011 #8
    Then, there is Octave and Freemat. And of course, Python with numpy/scipy.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Similar Discussions: Autocorrelation in Excel
  1. Excel code (Replies: 1)

  2. Histograms in Excel (Replies: 4)

  3. Excel summing (Replies: 3)

  4. Excel + Calculations (Replies: 9)

Loading...