Determine Correlation between data using Excel

As your know, Correlation is a measure of the relationship between two or more sets of data. For example, if we have  monthly figures for advertising expenses and sales, you might wonder whether they are related. That is, do higher advertising expenses lead to more Sales?

To determine this, we need to calculate correlation coefficient is a number between -1 and 1. Refer below:

  1. Correlation Coefficient is 1: The two sets of data are perfectly and positively correlated. e.g 10% increase in advertising will produce 10% increase in Sales
  2. Correlation Coefficient between 0 and 1:The two sets of data are positively correlated (an increase in advertising leads to an increase in Sales). The higher the number, the higher the correlation between the data
  3. Correlation Coefficient between 0 and -1: The two sets of data are negatively correlated (an increase in advertising leads to decrease in Sales). The lower the number, the more negatively correlated the data is.
  4. Correlation Coefficient is -1: The two data sets have a perfectly negative correlation. Eg. 10% increase in advertising will produce 10% decrease in Sales.

Refer below data set:

In the above shown data, there is high correlation between Discounts and Sales as compared to correlation between advertising and Sales.

And if a company having limited resources have to choose between Advertising and Discounts, they will go with discounts as it can have an immediate effect on increasing Sales.

Kindly share the blog with your friends also & make them awesome in excel.

Click here to Download Practice file (24 downloads)

Be the first to comment

Leave a Reply

Your email address will not be published.


*