{"id":605,"date":"2019-04-17T16:28:51","date_gmt":"2019-04-17T13:28:51","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=605"},"modified":"2019-05-06T13:12:51","modified_gmt":"2019-05-06T10:12:51","slug":"excel-statistics-calculating-the-standard-deviation","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/excel-statistics-calculating-the-standard-deviation\/","title":{"rendered":"Excel Statistics &#8211; Calculating the Standard Deviation"},"content":{"rendered":"\n<p>One of the most useful measures in statistics is that of <strong>standard deviation<\/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 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\/DioEVNMtZmU?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\/Standard-Deviation.xlsx\">Standard Deviation<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/Standard-Deviation.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 standard\ndeviation is 1.5. Let\u2019s assume that the student in question has to take one\nmore test tomorrow and we have to guess what its grade will be. It would be\nsafe to say that he would be graded somewhere around his average grade. This is\nwhere standard deviation kicks in. It defines approximately how much around the\naverage value we would expect the new value to be. So in our example the\nexpected grade is more probable to be between the range of 5 and 8. 5 is\nderived from the subtraction of 1.5 from 6.5 and 8 from the addition of the\naverage 6.5 with 1.5 the standard deviation.<\/p>\n\n\n\n<p>When we have only a sample of the data from a population,\nthen the standard deviation we calculate is called sample standard deviation\nand when the data from the whole population is available then it is called\npopulation standard deviation.<\/p>\n\n\n\n<p>Excel has 6 functions to calculate standard deviation. 3 of\nthem calculate the sample standard deviation and 3 the population standard\ndeviation.<\/p>\n\n\n\n<p>Although STDEV and STDEVP functions can still be used in\noffice 2016 for compatibility, they have been replaced by STDEV.S and STDEV.P\nrespectively, and should be avoided, since they might not be supported in\nfuture versions of Excel.<\/p>\n\n\n\n<p>STDEV.S calculates the sample standard deviation and STEDV.P\nthe population standard deviation.<\/p>\n\n\n\n<p>STDEVA and STDEVPA also calculate the sample and population\nstandard deviation respectively. Their difference to the previous functions is\nto the way they treat logical values and text when they are part of our\npopulation. <\/p>\n\n\n\n<p>STEDV.S and STEDV.P ignore text and logical values, while\nSTDEVA and STDEVPA count the text as zero and the logical values as 1 and zero\nfor TRUE and 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 Standard deviation using the STDEV.P and in cell B11 its alternative STDEVPA.<\/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\nstandard deviation using 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 STDEVA 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 standard\ndeviation. <\/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 STDEVA and STDEVPA<\/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 standard deviation. 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 plenty of other online sources if you want to explore the math behind it.<\/p>\n","protected":false},"author":2,"featured_media":608,"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,61,63,64,76,62,77],"class_list":["post-605","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-statistical","tag-stdev","tag-stdev-p","tag-stdev-s","tag-stdeva","tag-stdevp","tag-stdevpa"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/stdev.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9L","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/605","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=605"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/605\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/608"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}