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.

## How to Calculate Autocorrelation in Excel: 2 Ways

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.

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**.

** Steps**:

Firstly, we need to calculate the total number of months to determine 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**.

âž¤ After pressing **ENTER**, you will get the total number of months; **9**.

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`

âž¤ Press **ENTER **and drag down the **Fill Handle **tool.

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

**Read More: **How to Do Correlation and Regression Analysis in Excel

__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**,

**, and**

*2***.**

*3*** 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**.

âž¤ After pressing **ENTER**, you will get the total number of months; **9**.

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`

`Â`

âž¤ Press **ENTER**.

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

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.

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.

**Read More:** How to Calculate Cross Correlation in Excel

## 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 have a positive value which represents a proportionate increase in this time interval. And, for the rest of the lag values, we have negative autocorrelations defining a proportionate decrease in the time intervals.

**Read More:** How to Make Correlation Graph in Excel

## 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.

**Download Workbook**

## 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

- How to Make a Correlation Scatter Plot in Excel
- Find Correlation Between Two Variables in Excel
- How to Calculate Correlation between Two Stocks in Excel
- How to Make a Correlation Table in Excel
- How to Make a Correlation Matrix in Excel
- How to Interpret Correlation Table in Excel
- How to Make Correlation Heatmap in Excel
- How to Calculate Partial Correlation in Excel

**<< Go Back toÂ Excel CorrelationÂ |Â Excel for Statistics****Â |Â Learn Excel**

Hi,

Interesting article. Thank you for sharing, but I am still confused about how to apply what you have shared in my application.

I have digitized vibration sensor data from rotating machinery. My set of values that I paste into the Excel column is 1808 cells long. This is the number of samples that are acquired in a single rotation of the shaft.

I am not an expert in Excel, or statistics. My understanding is that Autocorrelation should always result in values between +1 and -1. I would like to have an output that I can plot in a x-y line chart or better yet in a “chart” that represents one shaft rotation (for visualization purposes).

I do not know how many (lag) segments to use but once I understand how to properly configure this I could experiment with lag to find what might be appropriate.

Do you have a recommendation for how I should approach this?

Hello Allen,

Thank you for sharing your experience with us. I understand you want to know how the lag segments work to properly configure your digitized vibration sensor data from rotating machinery.

Here are factors to consider:

1. Longer data means you can use more lag values without losing reliability.

2. More frequent data collection might need more lag values to find detailed patterns.

3. If you expect specific patterns, use enough lag values to catch them.

4. To detect repeating patterns, use enough lags to cover potential periods.

5. More lags require more calculations, potentially slowing processing. Balance accuracy and efficiency based on available resources.

6. With your data resolution, you could start with around 40-50 lags to capture potential patterns within a rotation.

7. Finally, there’s no universally optimal lag length. It depends on your data, analysis objectives, and computational constraints.

Let’s calculate the autocorrelation with a data range similar to yours using a different technique. Since you have significantly large data, you can consider this method. I am going to calculate autocorrelation using Excel VBA to make the process faster and more accurate. Follow the steps:

1. Line up your data.

2. Run the below VBA code:

As a result, you will obtain the scatter chart visualizing your output.

You can use the effective methods mentioned in the article too. Hope this helps!

Regards,

Yousuf Khan Shovon