{"id":610,"date":"2019-04-17T16:52:04","date_gmt":"2019-04-17T13:52:04","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=610"},"modified":"2019-05-06T13:13:38","modified_gmt":"2019-05-06T10:13:38","slug":"excel-statistics-calculating-the-variance","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/excel-statistics-calculating-the-variance\/","title":{"rendered":"Excel Statistics &#8211; Calculating the Variance"},"content":{"rendered":"\n<p>One of the most useful measures in statistics is that of <strong>variance<\/strong>. It is a measure that is used to quantify the amount of variation or dispersion of a set of data values. I\u2019ll try to explain what this means with an example, but there are plenty of other online sources if you want to explore the math behind it.<\/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\/tY2CM7BXLCs?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\/VARVAR.SVAR_.PVARP_.xlsx\">VAR,VAR.S,VAR.P,VARP<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/VARVAR.SVAR_.PVARP_.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>Let\u2019s assume the following population of numbers. These\nnumbers are the exam grades for a student.<\/p>\n\n\n\n<p>The average of this grades is 6.5. And the variance is 2.3.\nVariance and the mean average are not expressed in the same unit of\nmeasurement. To have a comparable measure in the same unit we have to calculate\nthe square root of the variance. This provides us with the standard deviation,\nwhich we discuss in another of our lessons. <\/p>\n\n\n\n<p>When we have only a sample of the data from a population,\nthen the variance we calculate is called sample variance and when the data from\nthe whole population is available then it is called population variance.<\/p>\n\n\n\n<p>Excel has 6 functions to calculate variance. 3 of them\ncalculate the sample variance and 3 the population variance.<\/p>\n\n\n\n<p>Although VAR and VARP functions can still be used in office\n2016 for compatibility, they have been replaced by VAR.S and VAR.P\nrespectively, and should be avoided, since they might not be supported in\nfuture versions of Excel.<\/p>\n\n\n\n<p>VAR.S calculates the sample variance and VAR.P the\npopulation variance.<\/p>\n\n\n\n<p>VARA and VARPA also calculate the sample and population variance\nrespectively. Their difference to the previous functions is to the way they\ntreat logical values and text when they are part of our population. <\/p>\n\n\n\n<p>VAR.S and VAR.P ignore text and logical values, while VARA\nand VARPA count the text as zero and the logical values as 1 and zero for TRUE\nand False respectively.<\/p>\n\n\n\n<p>Let\u2019s see an example.<\/p>\n\n\n\n<p>This example has mostly the same data with some minor\ndifferences. We have already calculated the mean average as 6.4.<\/p>\n\n\n\n<p>Since this table consists of all of the grades for a certain\nstudent we assume this table is our whole population.<\/p>\n\n\n\n<p>So in cell B10 we will calculate the Variance using the VAR.P and in cell B11 its alternative VARPA.<\/p>\n\n\n\n<p>In B10 we type the name of the function and select the data\nset. The values of SICK and FALSE are ignored during the calculation.<\/p>\n\n\n\n<p>In B11 as before we type the function and the cell range.\nThe value FALSE is calculated now as 0 and the same goes with value SICK. This\nis the reason for the different result. Otherwise both the functions should\nproduce the same result.<\/p>\n\n\n\n<p>Let\u2019s assume, for the sake of this example, that we only had\nthe exam results for the months from August to December. That means we would\nhave only a sample of the whole population.<\/p>\n\n\n\n<p>So in cells B12 and B13 we will calculate the sample variance\nusing the data for these months.<\/p>\n\n\n\n<p>In B12 we type the name of the function same as before and\nthe cell range, I3 to M6.<\/p>\n\n\n\n<p>We do the same in B13 but using the VARA function instead.<\/p>\n\n\n\n<p>Notice the difference between the 2 sets of functions. The\nones that calculate the sample and the ones that calculate the population variance.\n<\/p>\n\n\n\n<p>If this was a real life statistical problem, we should\nchoose to use the functions that consider the absences of the student as\nzero-graded instead of ignoring them. So we should go with VARA and VARPA\nfunctions<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>One of the most useful measures in statistics is that of variance. It is a measure that is used to quantify the amount of variation or dispersion of a set of data values. I\u2019ll try to explain what this means with an example, but there are plenty of other online sources if you want to explore the math behind it.<\/p>\n","protected":false},"author":2,"featured_media":612,"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":[14,65,68,67,78,66,79],"class_list":["post-610","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-statistical","tag-var","tag-var-p","tag-var-s","tag-vara","tag-varp","tag-varpa"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/var.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9Q","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/610","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=610"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/610\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/612"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=610"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=610"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=610"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}