Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Cross Correlation in Excel (2 Suitable Ways)

In statistical analysis, calculating cross-correlation is a common task. It may be quite tricky and time-consuming if you try to find it in the traditional mathematical way. But in Excel, you can do it brilliantly and fast. So you will learn 2 quick ways to calculate cross-correlation in Excel from this article with proper illustrations.


Download Practice Workbook

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


What Is Cross Correlation?

Cross-correlation is a statistical term. It defines the relationship between two or more variables in terms of time. It tracks how one or more variables change with the change of another variable. We can also use multiple time series to track the amount of variation. From different time scales, we will find out the maximum similarity or dissimilarity.

The amount of correlation coefficient ranges from +1 to -1.

+1 means the variable is identical and -1 means opposite one another. And when this is 0 means, there is no relationship between the variables.

The mathematical formula for calculating the cross-correlation is given below:

Here,

r = Cross-correlation coefficient

n = Number of data available

xy =Sum, and product of the data of the first and second variables.

x = Sum of the first variable

y = Sum of the second variable

x2 =Square sum of each data of the first variable

y2 =Square sum of each data of the second variable

In simple words, we can explain the cross-correlation. We consider we have a dataset containing columns X, Y, and Z. Here, X is independent and Y and Z are dependent. If X and Y are positively correlated, then when X increases Y will also increase.

Again, if X and Z are the same positively co-related, Z will increase as X increases. So, Y and Z are said to be cross-correlated, as both of them show the same behavior concerning X.


2 Ways to Calculate Cross Correlation in Excel

In this article, we will show 2 examples to calculate cross-correlation in Excel. Those examples are based on the CORREL function and Add-Ins of Excel. Those examples are based on the CORREL function and Add-Ins of Excel. We will consider the following dataset.

Sample data to calculate cross correlation in Excel

We will calculate the cross-correlation of Production and Revenue based on the Investment.


1. Calculate Cross Correlation Without Time Lag

We can calculate cross-correlation without time lag in 2 ways- the CORREL function and the Data Analysis tool.

i. Using Excel CORREL Function

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

Here, we will 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:

  • We add new rows in the dataset to find the correlation efficiency.

  • First, we will calculate the correlation coefficient between Investment, Production, and Investment, 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

We can see both cases correlation coefficient is close to 1. This means Production and Revenue are both positively co-related with Investment. Now, we will 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

Here, we will use default Add-ins of Excel to calculate the cross-correlation.

📌 Steps:

  • Go to File >> Options >> Add-ins.
  • After that, select Add-ins and then the Go button.

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

Select Add-ins for Cross Correlation

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

  • Now, click on the Data Analysis option in the Data tab.

  • Select the Correlation option from the Data Analysis window.
  • Finally, press the OK button.

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

Calculate Cross Correlation with Excel Add-ins

  • Then, click on the OK button.

We can see the correlation coefficients are shown here.


2. Calculate Cross Correlation with Time Lag Using CORREL Function

Here, we will consider time lag to calculate the correlative coefficient. It is assumed that investment in the company will not reflect immediately. It may be seen that after a particular time period investment dominate production and revenue.

📌 Steps:

  • First, we calculate the correlative coefficient considering different lags in the Range H5:I9 using the CORREL function.

We can see for lag 3, we get the maximum coefficient value.

  • Now, 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


Conclusion

In this article, we showed 2 ways to calculate the cross-correlation in Excel with proper explanation. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

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

ExcelDemy
Logo