If you want to do some Statistical calculations, you can easily use correlation in Excel. This is one of the easiest statistical calculations in Excel. Correlation can be useful to correlate two or more variables. This article will provide an overview of how to interpret a correlation table in Excel with a suitable example. I hope you’ll enjoy the whole article and get a deep knowledge about correlation.
What Is Correlation in Excel?
Correlation can be demonstrated as a measure that shows the relation between two or more variables. It describes the strength and direction of two variables. If one variable changes in value, the other will automatically change in a specific direction. This process is useful because we can use a value and predict the other variable. To compute correlation, you need to invest more time. But Excel makes it easier to use.
Explanation of Correlation Formula
As you can see the correlation measures the relationship between two variables, first, we need to understand the correlation coefficient formula through which it calculates the required relation.
The correlation coefficient formula denotes:
Here, X and Y denote the same mean values of array 1 and array 2. Array 1 demonstrates a set of independent variables whereas array 2 defines a set of dependent variables. Now, if the correlation coefficient is +1 then it shows a strong positive relationship between the two variables which means if one value changes in a direction then the next value changes in a similar direction. A correlation coefficient of -1 means a strong negative correlation.
How to Create Correlation Matrix in Excel
To create a correlation matrix in Excel, we take a dataset that includes some person’s height, weight, and corresponding body mass index.
To form a correlation, you need to follow the following steps.
Steps
- First, need to enable the Analysis Toolpak. To enable this, go to the File tab in the ribbon and select the More command.
- From the More command, select options.
- It will open up the Excel Options dialog box, From there, select Add-ins. Finally, click on Go.
- An Add-ins dialog box will appear. From the Add-ins available section, select Analysis Toolpak and then click on OK. It will enable the Analysis Toolpak.
- Go to the Data tab in the ribbon and select Data Analysis from the Analysis group.
- In the Data Analysis dialog box, select Correlation from the Analysis Tools section and select OK.
- A Correlation dialog box will appear. From the Input section, select your Input Range and Select your preferred Output Range. Finally, click on OK.
- This will eventually produce a correlation matrix in your desired direction.
Read More: How to Make a Correlation Table in Excel
Interpret Correlation Table in Excel
The correlation coefficient between two variables measures not only the strength but also the direction of the relationship between the two variables. The coefficient value lies between – 1 and +1.
Strength
The correlation coefficient denotes the relationship strength between two variables.
- The maximum value can be either +1 or -1 in a different direction. These maximum values denote the perfect and strong relationship between variables. But in reality, it is tough to find out this kind of relationship.
- When the correlation coefficient is 0. It means there is no relationship between the two variables.
- If the coefficient values are in between o and -1/+1 then they have a good relationship but are not perfect. If the coefficient value gets closer to 1 then they have a good relationship whereas if the value gets closer to 0, then they have a relationship that is weak.
Direction
The correlation coefficient sign denotes the direction of the relationship between two variables.
- A positive value of the coefficient denotes a direct relationship. On a graph, it provides an upward slope.
- A negative value of the coefficient denotes a reverse relationship. On a graph, it provides a downward slope.
1. Interpreting Correlation Between Height and Weight
First of all, the correlation between height and weight shows the exact relationship between them and shows how one value changes by altering the other value. Here, the correlation coefficient is 0.7943 which means they have a positive direct relationship. The value is close to 1 which means they maintain a strong relationship. As they have a positive and strong relationship, we will get an upward slope on a graph. If you look at the graph, we will see there is one value that deviates from the standard upward slope. This is a rare case when someone has a good height but has less weight than expected.
Read More: How to Make Correlation Graph in ExcelÂ
2. Interpreting Correlation Between Height and Body Mass Index
The correlation between height and body mass index(BMI) is -0.4731. As the coefficient value is negative that means they form a reverse relation and if the value is between 0 and 1 it means it produces a moderate relationship. The graph shows a similar result. In one certain case, BMI increases because BMI also depends on the weight of that person. In other results, if the height of a person increases, BMI will decrease automatically.
Read More: How to Make a Correlation Scatter Plot in Excel
3. Interpreting Correlation Between Weight and Body Mass Index
Finally, the correlation between weight and body mass index is 0.156708. The correlation coefficient value is positive and close to 0. It denotes that they have a positive relationship in the upward direction but also have a weak relation. As the value tends to zero, it provides a slightly upward direction. Again, BMI not only depends on weight but also height. That’s why you see some deviation on the graph.
Read More: How to Make Correlation Heatmap in Excel
Download Practice Workbook
Download this practice workbook
Conclusion
We have discussed step by step process of how to interpret a correlation table in Excel and also how to create a correlation matrix easily. I think you enjoy the whole article and take some valuable inside. If you have any questions feel free to ask in the comment section.
