How to Calculate Autocorrelation in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to calculate autocorrelation in Excel, then you will find this article useful. Autocorrelation or serial correlation is helpful to determine the relationship between a data series over time with its lagged version by different amounts.
So, let’s dive into the main article to know more about this issue.

Download Workbook


2 Ways to Calculate Autocorrelation in Excel

Here, we have the following dataset containing the sales records over different months of a company. We will try to determine the autocorrelation for different lags in these records between these time ranges using the following 2 ways.

how to calculate autocorrelation in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using SUMPRODUCT, OFFSET, AVERAGE, and DEVSQ Functions to Calculate Autocorrelation

Here, we will use the combination of the SUMPRODUCT, OFFSET, AVERAGE, and DEVSQ functions to calculate the autocorrelation of the sales values for a range of lags between 1 to 5.

how to calculate autocorrelation in Excel

Steps:
Firstly, we need to calculate the total number of months for determining the time series of the sales values.
➤ Type the following function in cell D13.

=COUNTA(B4:B12)

COUNTA will determine the total months in the range B4:B12.

SUMPRODUCT, OFFSET, AVERAGE, DEVSQ functions

➤ After pressing ENTER, you will get the total number of months; 9.

how to calculate autocorrelation in Excel

Now, we will determine the autocorrelations for the sales series between the lags 1 to 5.
➤ Write down the following formula in cell G4.

=SUMPRODUCT(OFFSET($D$4:$D$12,0,0,$D$13-F4)-AVERAGE($D$4:$D$12),OFFSET($D$4:$D$12,F4,0,$D$13-F4)-AVERAGE($D$4:$D$12))/DEVSQ($D$4:$D$12)

Here, $D$4:$D$12 is the Sales range, $D$13 is the total number of months, and F4 is the lag value.

  • $D$13-F4 becomes
    9-1 → 8
  • OFFSET($D$4:$D$12,0,0,$D$13-F4) becomes
    OFFSET($D$4:$D$12,0,0,8) → extracts a range with a height of 8 rows from the reference cell $D$4.
    Output → {4996; 4137; 3203; 3403; 4831; 4931; 4753; 4381}
  • AVERAGE($D$4:$D$12) → determines the average value of this range
    Output → 4367.555
  • OFFSET($D$4:$D$12,0,0,$D$13-F4)-AVERAGE($D$4:$D$12) becomes
    {4996; 4137; 3203; 3403; 4831; 4931; 4753; 4381}-4367.555
    Output → {628.444; -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444}
  • OFFSET($D$4:$D$12,F4,0,$D$13-F4) becomes
    OFFSET($D$4:$D$12,1,0,8) → the starting cell reference moves 1 cell downwards from $D$4 and then extracts a range with a height of 8 rows from the reference cell $D$5
    Output → {4137; 3203; 3403; 4831; 4931; 4753; 4381; 4673}
  • OFFSET($D$4:$D$12,F4,0,$D$13-F4)-AVERAGE($D$4:$D$12) becomes
    {4137; 3203; 3403; 4831; 4931; 4753; 4381; 4673}-4367.555
    Output → { -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444; 305.444}
  • SUMPRODUCT(OFFSET($D$4:$D$12,0,0,$D$13-F4)-AVERAGE($D$4:$D$12),OFFSET($D$4:$D$12,F4,0,$D$13-F4)-AVERAGE($D$4:$D$12)) becomes
    SUMPRODUCT({628.444; -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444},{ -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444; 305.444})
    Output → 1287454.358
  • DEVSQ($D$4:$D$12) → returns the sum of squares of the deviations of the data range from their mean.
    Output → 3508950.222
  • SUMPRODUCT(OFFSET($D$4:$D$12,0,0,$D$13-F4)-AVERAGE($D$4:$D$12),OFFSET($D$4:$D$12,F4,0,$D$13-F4)-AVERAGE($D$4:$D$12))/DEVSQ($D$4:$D$12) becomes
    1287454.358/3508950.222
    Output → 0.366905848

SUMPRODUCT, OFFSET, AVERAGE, DEVSQ functions

➤ Press ENTER and drag down the Fill Handle tool.

SUMPRODUCT, OFFSET, AVERAGE, DEVSQ functions

After that, you will get the autocorrelations for the monthly sales series between a range of lags from 1 to 5.

how to calculate autocorrelation in Excel

Read More: How to Find Correlation between Two Variables in Excel


Similar Readings


Method-2: Using SUMPRODUCT, AVERAGE, VAR.P Functions to Calculate Autocorrelation in Excel

In this section, we are going to use the combination of SUMPRODUCT, AVERAGE, and VAR.P functions to have the autocorrelation for the monthly sales series with their lagged versions for lag values 1, 2, and 3.

how to calculate autocorrelation in Excel

Steps:
➤ Type the following function in cell D13 to determine the total number of rows in the data series.

=COUNTA(B4:B12)

COUNTA will determine the total months in the range B4:B12.

SUMPRODUCT, AVERAGE, VAR.P functions

➤ After pressing ENTER, you will get the total number of months; 9.

how to calculate autocorrelation in Excel

It’s time to determine the autocorrelations for the sales series between the lags 1 to 3.
➤ Type the following formula in cell G4.

=(SUMPRODUCT(D4:D11-AVERAGE(D4:D12),D5:D12-AVERAGE(D4:D12))/D13/VAR.P(D4:D12))

Here, D4:D11 is the Sales range without the last cell value due to lag 1, similarly, D5:D12 is the Sales range without the first cell value due to lag 1, and D13 is the total number of months.

  • AVERAGE(D4:D12) → determines the average value of this range
    Output → 4367.555
  • D4:D11-AVERAGE(D4:D12) becomes
    {4996; 4137; 3203; 3403; 4831; 4931; 4753; 4381}-4367.555
    Output → {628.444; -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444}
  • D5:D12-AVERAGE(D4:D12) becomes
    {4137; 3203; 3403; 4831; 4931; 4753; 4381; 4673}-4367.555
    Output → { -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444; 305.444}
  • SUMPRODUCT(D4:D11-AVERAGE(D4:D12),D5:D12-AVERAGE(D4:D12)) becomes
    SUMPRODUCT({628.444; -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444},{ -230.556; -1164.556; -964.556; 463.444; 563.444; 385.444; 13.444; 305.444})
    Output → 1287454.358
  • SUMPRODUCT(D4:D11-AVERAGE(D4:D12),D5:D12-AVERAGE(D4:D12))/D13 becomes
    1287454.358/9
    Output → 143050.484224966
  • P(D4:D12) → determines variance based on the entire range.
    Output → 389883.358024691
  • (SUMPRODUCT(D4:D11-AVERAGE(D4:D12),D5:D12-AVERAGE(D4:D12))/D13/VAR.P(D4:D12)) becomes
    (143050.484224966/389883.358024691)
    Output → 0.366905848 

SUMPRODUCT, AVERAGE, VAR.P functions

➤ Press ENTER.
In this way, you will get the autocorrelation of the sales series with their one lagged version.

SUMPRODUCT, AVERAGE, VAR.P functions

Similarly, to get the autocorrelation for lag=2 use the following formula.

=(SUMPRODUCT(D4:D10-AVERAGE(D4:D12),D6:D12-AVERAGE(D4:D12))/D13/VAR.P(D4:D12))

D4:D10 represents the sales series without the last two values due to lag 2 and D6:D12 is the series without the first two values.

SUMPRODUCT, AVERAGE, VAR.P functions

In the same way, you can have the autocorrelation for lag=3 by applying the following formula.

=(SUMPRODUCT(D4:D9-AVERAGE(D4:D12),D7:D12-AVERAGE(D4:D12))/D13/VAR.P(D4:D12))

D4:D9 represents the sales series without the last three values due to lag 3 and D7:D12 is the series without the first three values.

how to calculate autocorrelation in Excel

Read More: How to Do Correlation in Excel (3 Easy Methods)


Comparison of Results with Graphical Representation

As you can see, by using the above two formulas in two methods we are getting exactly the same autocorrelation values for their corresponding lags. Moreover, it can be concluded that with the increasing lagged values we are having a series of decreased autocorrelations.
For the lag value 1, we are having a positive value which represents a proportionate increase in this time interval. And, for the rest of the lag values, we are having negative autocorrelations defining a proportionate decrease in the time intervals.

Graphical Representation


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to calculate autocorrelation in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo