How to Calculate Cross Correlation in Excel: 2 Suitable Ways

Method 1 – Calculate Cross Correlation Without Time Lag

i. Using Excel CORREL Function

The CORREL function returns the correlation coefficient between two data sets.

Use the CORREL function to calculate cross-correlation without time lag. As we will not consider time lag, we will consider the whole dataset for calculation.

Steps:

  • Add new rows in the dataset to find the correlation efficiency.

  • We will calculate the correlation coefficient between Investment, Production, and Investment and revenue by applying the following formulas.

On Cell D16:

=CORREL(C5:C14,D5:D14)

On Cell D17:

=CORREL(C5:C14,E5:E14)

Calculate Cross Correlation with CORREL Function

See both cases correlation coefficient is close to 1. This means Production and Revenue are both positively co-related with Investment. Find out the cross-correlation coefficient between Production and Revenue.

  • Put the following formula on Cell D18.
=CORREL(D5:D14,E5:E14)

This result is also close to 1. So, Production and Revenue will show similar behavior. If Production increases, Revenue will also increase.

We can also plot the following dataset in a line chart.

This chart clearly indicates that the other two variables Production and Revenue are positively related to the Investment.


ii. Using the Data Analysis Tool of Analysis-ToolPak

Use default Add-ins of Excel to calculate the cross-correlation.

Steps:

  • Go to File >> Options >> Add-ins.
  • Select Add-ins and then the Go button.

  • The Add-ins window appears.
  • Choose Analysis Toolpak add-in from the list.
  • Press OK.

Select Add-ins for Cross Correlation

This add-is has been attached to the main tab of Excel.

  • Click on the Data Analysis option in the Data tab.

  • Select the Correlation option from the Data Analysis window.
  • Press the OK button.

  • The Correlation window appears.
  • Choose the Input Range from the dataset. We choose the Investment, Production, and Revenue columns.
  • Tick the Labels in first row option.
  • Select a cell as the Output Range.

Calculate Cross Correlation with Excel Add-ins

  • Click on the OK button.

We can see the correlation coefficients are shown here.


Method 2 – Calculate Cross Correlation with Time Lag Using CORREL Function

Steps:

  • Calculate the correlative coefficient considering different lags in the Range H5:I9 using the CORREL function.

We can see for lag 3; Get the maximum coefficient value.

  • Use lag 3 for calculating the cross-correlative coefficient using the following formula.
=CORREL(D5:D11,E8:E14) 

Cross Correlation with Time Lag

We calculated the cross-correlation for lag 3.

Here is a graph for cross-correlation with time lag 3.

Graphical presentation of Cross correlation with time lag


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. this post does not deal with cross-correlation

    • Hi Don. Thanks for letting us know about this. 🙂 We have updated the blog post. Regards
      -ExcelDemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo