{"id":614,"date":"2019-04-17T16:58:17","date_gmt":"2019-04-17T13:58:17","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=614"},"modified":"2019-05-06T12:25:57","modified_gmt":"2019-05-06T09:25:57","slug":"excel-statistics-calculating-the-covariance-and-the-correlation","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/excel-statistics-calculating-the-covariance-and-the-correlation\/","title":{"rendered":"Excel Statistics &#8211; Calculating the Covariance and the Correlation"},"content":{"rendered":"\n<p>In statistics, <strong>covariance<\/strong> and <strong>correlation<\/strong> are measures of how much two random variables change together.&nbsp; A positive number would indicate a positive linear relationship between the variables and a negative number would indicate the opposite.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/oIjV3YwJZsE?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/COVARCOVARIANCE.SCOVARIANCE.PCORREL.xlsx\">COVAR,COVARIANCE.S,COVARIANCE.P,CORREL<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/COVARCOVARIANCE.SCOVARIANCE.PCORREL.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>A common simple example to better understand covariance and\ncorrelation is that with the balloon. <\/p>\n\n\n\n<p>As a balloon is inflated it gets larger and all its\ndimensions get larger respectively. The height, the width, and the depth of the\nballoon increase.&nbsp; This means that in\nsets of two these dimensions have a positive linear relationship.<\/p>\n\n\n\n<p>When we have finished filling the balloon with air we seal\nits open end. While sealed we squash the balloon in one dimension, let\u2019s assume\nthe width.<\/p>\n\n\n\n<p>We notice that while the width is decreased the other two\ndimensions increase to manage the extra volume of air. This means that, when we\nsquash a balloon, the width and the length or the depth variables have a\nnegative linear relationship, while the length and depth have a positive linear\nrelationship since they both increase.<\/p>\n\n\n\n<p>Correlation in addition to telling us whether variables are\npositively or negatively related, also tells us the degree to which the\nvariable tend to move together. Its value can be from -1 to 1.<\/p>\n\n\n\n<p>Excel provides us with three functions to calculate\ncovariance and one for correlation. <\/p>\n\n\n\n<p>The COVAR function which is still present in Excel 2016 for\ncompatibility reasons. It has been replaced by COVARIANCE.P and we should avoid\nusing it since its support could seize in future versions of Excel.<\/p>\n\n\n\n<p>The COVARIANCE.P which is used when the supplied data are\nthe whole population, and <\/p>\n\n\n\n<p>the COVARIANCE.S which is used when our data consist only a\nsample of the entire population.<\/p>\n\n\n\n<p>To calculate Correlation, we only need the CORREL function.<\/p>\n\n\n\n<p>So now that we have a general idea of what covariance and\ncorrelation mean and what functions to use, we can go on and put our knowledge\nto a test.<\/p>\n\n\n\n<p>In this sheet we can see all the exam results of a student\nfor the current year.<\/p>\n\n\n\n<p>In cell B8 we will calculate the covariance between Physics\nand Mathematics assuming that our data consist the entire population.<\/p>\n\n\n\n<p>First we type the function COVARIANCE.P, since we use the\nwhole population for physics and mathematics and then the two cell ranges that\nreflect our data.<\/p>\n\n\n\n<p>We have a positive result which means that physics and\nMathematics have a positive linear relationship. When one result is better,\nchances are that the second one will be better too.<\/p>\n\n\n\n<p>Then let\u2019s assume we only had the data displayed with yellow\nbackground, which are only a sample of the entire population, and we need to\ncalculate the covariance between Physics and History in cell B9.<\/p>\n\n\n\n<p>We will use the COVARIANCE.S function. As before we will\nprovide the two cell ranges in question.<\/p>\n\n\n\n<p>The result is a negative one which means that as the exam\nresults of Physics (and Mathematics as we have seen before) are getting better\nthe exams in History are getting a lot worst.<\/p>\n\n\n\n<p>For your information: If we calculate the covariance between\ntwo identical sets, for example Physics and Physics, the result will be the\nsame as the Variance of the dataset Physics.<\/p>\n\n\n\n<p>To compute the correlation coefficient between Physics and\nMathematics in cell B15 we use the Correl function, providing the two relevant\ncell ranges.<\/p>\n\n\n\n<p>Using the same function, we calculate the correlation\nbetween Physics and History. We notice that the correlation between Physics and\nHistory is negative and between Physics and Mathematics positive as with\ncovariance.<\/p>\n\n\n\n<p>There is a lot of Math behind covariance and correlation,\nwhich surpass the objective of this lesson. Luckily there are a lot of online\nresources on the subject for anyone who wants to dive in the hard stuff.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In statistics, covariance and correlation are measures of how much two random variables change together.  A positive number would indicate a positive linear relationship between the variables and a negative number would indicate the opposite.<\/p>\n","protected":false},"author":2,"featured_media":615,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[74,70,71,69,73,72,14],"class_list":["post-614","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-correl","tag-correlation","tag-covar","tag-covariance","tag-covariance-p","tag-covariance-s","tag-statistical"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/covar.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9U","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=614"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/614\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/615"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}