# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] autocorrelation function (ACF)

## Rado Lavrih

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

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

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

Thanks Jerry! Works fine!

Bye

----------


## Rado Lavrih

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

Regards

Rado

----------


## tryme83

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

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

----------

