Excel for Statistics > Calculate Covariance in Excel 2013
Watch the video or follow the steps below:
Covariance in Excel 2013: Overview
Covariance measures how changes in one variable affect another. It measures the degree the two values have a linear relationship. It’s similar to Pearson correlation. Both tell you if the two sets of numbers are related. Correlation gives you a value between -1 and +1. -1 is a perfect negative correlation and +1 is a perfect positive correlation. Covariance on the other hand, gives you some positive number if the variables are positively related. You’ll get a negative number if they are negatively related. A high covariance means there is a strong relationship between the variables. A low value means there is a weak relationship.
Covariance in Excel: Steps
Step 1: Enter your data into two columns in Excel. For example, type your X values into column A and your Y values into column B.
Step 2: Click the “Data” tab and then click “Data analysis.” The Data Analysis window will open.
Step 3: Choose “Covariance” and then click “OK.”
Step 4: Click “Input Range” and then select all of your data. Include column headers if you have them.
Step 5: Click the “Labels in First Row” check box if you have included column headers in your data selection.
Step 6: Click “Output Range” and then select an area on the worksheet. A good place to select is an area just to the right of your data set.
Step 7: Click “OK.” The covariance will appear in the area you selected in Step 5.
Tip: Run the correlation function in Excel after you run covariance in Excel 2013. Correlation will give you a value for the relationship. 1 is perfect correlation and 0 is no correlation. All you can really tell from covariance is if there is a positive or negative relationship.
Check out our YouTube channel for more Excel tips and help!
If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!