Forgot your password?

How to calculate correlation and dependence in Excel

By Edited Nov 13, 2013 0 0

Learning how to calculate correlation or dependence in Excel is a very simple but useful skill. Correlation calculation is a great way to determine if two sets of data are related in other words if one variable changes, does the second variable also change? It is also used to determine how much two sets of data are related to each other such as the relationship between price and demand. In other words, if the price for a product goes up, the demand for the product goes down. It is important to note that correlation or dependence does not imply causality. There may be a causal relationship between the two factors, but correlation in itself does not indicate that this is so.
A measure of the amount of correlation between two factors is correlation coefficient. If the correlation coefficient has a value of 1 or close to one, it indicates that the two data sets are highly correlated. If the value is 0 or very close to 0, the two factors are not correlated. If the correlation coefficient is equal to -1 or close to the value, the two factors negatively correlated. In other words, if one variable goes up the other one goes down. This number can also be used to evaluate the relative correlation between one set of factors versus another set of factors.

Things You Will Need

Microsoft Excel
Two data sets

Step 1

Enter corrolcation data
Enter the data for the first data set in one column and the second data set into a second column. Each data value in the first column must have a corresponding data value in the second column.

Step 2

set corrolcation function
Enter the function "=CORREL" in a separate cell. The function requires two sets of data to perform the correlation calculation.

Step 3

Enter first corrolcation data
Enter the first array of data by selecting the values for the first set. Be sure to select all of the data in the first set.

Step 4

Enter second corrolcation data
Type a "," in the function and select the data from the second set. Make sure that you end the function with a ")".

Step 5

corrolation coifficient
Push the return or tab button and the resulting number is the correlation coefficient. Array

Tips & Warnings

  • If the "CORREL" function is not recognized by Excel, it is because not all of the statistical functions have been loaded. To load the function, go to tools and load click "more functions", choose "statistical", then "CORREL".
  • Note that correlations work well for "straight line" relationships, but don't work as well with most curves. To determine correlation between two curves, you will need to perform some linear regression analysis.
  • Advertisement


    Add a new comment - No HTML
    You must be logged in and verified to post a comment. Please log in or sign up to comment.

    Explore InfoBarrel

    Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
    © Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

    Follow IB Technology