autocorrelation function (ACF)

Rado Lavrih · 07-12-2005, 07:05 AM

Hi!

wonder if anybody has knowledge of Excel add in that performs
autocorrelation function (ACF) and of the partial autocorrelation
function (PACF).

I need it for excel demonstration of Box Jenkinins Metholody for Arima
models in forecasting.

Thanks

Rado

Mike Middleton · 07-12-2005, 07:05 PM

Rado Lavrih -

ACF is easy to implement with worksheet functions SUMPRODUCT and OFFSET, as
shown in Chapter 18, Autocorrelation and Autoregression, of my book Data
Analysis Using Microsoft Excel.

If you do a Google search for "excel autocorrelation partial" (without the
quotes), you'll get numerous links.

- Mike
www.mikemiddleton.com

Jerry W. Lewis · 07-12-2005, 11:05 PM

Do a Google search on Box Jenkins Excel. There are a few add-ins and an
academic article.

=SUMPRODUCT(OFFSET(data,0,0,70-lag,1)-AVERAGE(data),OFFSET(data,lag,0,70-lag,1)-AVERAGE(data))/DEVSQ(data)

implements the definition of autocorrelation preferred in Box & Jenkins.

Jerry

Rado Lavrih · 07-13-2005, 03:05 PM

Thanks Jerry! Works fine!

Bye

Rado Lavrih · 07-14-2005, 04:05 PM

P.S.: Jerry is there such a function for PACF too?

Regards

Rado

**tryme83** · 04-04-2010, 09:38 PM

The formula can be simplified:
=PEARSON(OFFSET($data$,0,0,N-lag,1),OFFSET($data$,lag,0,N-lag,1))

where N=number of observations (data points)
lag=chosen period (try different periods to find optimum)
instead of pearson, also correl can be used:

=CORREL(OFFSET($data$,0,0,N-lag,1),OFFSET($data$,lag,0,N-lag,1))

**naveengd** · 07-05-2010, 06:30 AM

Hello,

I am looking for some help with autocorrelation in excel. I have historical data for past 3 years in weekly time intervals. I am interested to find the autocorrelation btw 1st week of each month / 1st week of each quarter. I tried to get this in Minitab but wasn't able to analyze the data.

Please help/advise.

Thanks,
Naveen G D