{"id":601,"date":"2019-04-17T16:21:24","date_gmt":"2019-04-17T13:21:24","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=601"},"modified":"2019-05-06T13:12:01","modified_gmt":"2019-05-06T10:12:01","slug":"excel-statistics-calculating-the-quartiles","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/excel-statistics-calculating-the-quartiles\/","title":{"rendered":"Excel Statistics &#8211; Calculating the Quartiles"},"content":{"rendered":"\n<p>In statistics, the quartiles, of a ranked set of data values, are the three points that divide the set into four equal groups. Each group constitutes a quarter of the data.<\/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\/pW3LIMwsXT4?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\/QUARTILES.xlsx\">QUARTILES<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/QUARTILES.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>The first quartile is the middle value between the smallest\nnumber and the middle number of the dataset. The second is the middle number of\nthe whole dataset. And the third is the middle value between the middle number\nand the highest number of the dataset.<\/p>\n\n\n\n<p>So the first quartile is at the 25% of our population of\nnumbers, the second at 50% and the third at 75%.<\/p>\n\n\n\n<p>Excel since its 2010 version implements two methods of\ncalculating quartiles. Let\u2019s try to explain the difference between them.<\/p>\n\n\n\n<p>In this example we have a group of 12 numbers in ascending\norder. The inclusive function uses the (n-1) method to calculate the quartiles\nposition. N is the total numbers we have which is 12 in our example, so n-1\nequals 11.<\/p>\n\n\n\n<p>If we multiply eleven by 0,25 we get the position of the\nfirst quartile which is in our case is 2,75. In position 2 we have the number 8\nand in position 3 the number 12. Using simple mathematics, we can calculate the\nnumber in position 2.75 as the number 11. Using the same technique, we can\ncalculate the second and third quartile as well. Excel saves us all this\ntrouble with the inclusive version of quartile.<\/p>\n\n\n\n<p>The exclusive version uses the (n+1) method to calculate the\nquartiles position. N is 12 so N+1 equals 13.<\/p>\n\n\n\n<p>We multiply this number by 0.25 as before we get the position of the first quartile which in our case is 3.25. Notice that all numbers are shifted one place to the right. This places the first quartile between 8 and 12 again but at a different relative position between them. So the first quartile now is the number 9. The same applies to the rest of the quartiles. The exclusive version of quartile does the heavy lifting and calculates the quartiles using this method.<\/p>\n\n\n\n<p>Let us try to use these function in the following example.<\/p>\n\n\n\n<p>Suppose we want to calculate the third quartile for all the\ngrades, of all the lessons in the table.<\/p>\n\n\n\n<p>We use the <a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-quartile-inc-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"inclusive version (opens in a new tab)\">inclusive version<\/a>. Then select the table, and finally number three, for the third quartile.<\/p>\n\n\n\n<p>We repeat the same procedure using the <a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-quartile-exc-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"exclusive version (opens in a new tab)\">exclusive version<\/a>. We select the table, and then number 3.<\/p>\n\n\n\n<p>We notice the difference of the results they produce is\nsmall.<\/p>\n\n\n\n<p>For compatibility reasons the older <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-quartile-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Quartile  (opens in a new tab)\">Quartile <\/a><\/strong>function is still supported at the 2016 version of Excel. <\/p>\n\n\n\n<p>We mentioned before that the quartiles reside at the 25, 50\nand 75 percent of our population of numbers. What if we want to know the point\nof the 90% of our dataset?<\/p>\n\n\n\n<p>In this case we use the <a rel=\"noreferrer noopener\" aria-label=\"percentile  (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-percentile-function\/\" target=\"_blank\">percentile <\/a>function. It too offers an <a rel=\"noreferrer noopener\" aria-label=\"inclusive  (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-percentile-inc-function\/\" target=\"_blank\">inclusive <\/a>and <a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-percentile-exc-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"exclusive  (opens in a new tab)\">exclusive <\/a>version.<\/p>\n\n\n\n<p>In the following example we can see the 90% mark in both\ncomputing methods. As we explained before we multiply (n-1) by 0.9 for the\ninclusive method and (n+1) by 0.9 for the exclusive.<\/p>\n\n\n\n<p>The manual calculation of the percentiles here is a bit more\ndifficult than before but luckily excel saves the day.<\/p>\n\n\n\n<p>Let\u2019s see a more practical example.<\/p>\n\n\n\n<p>We need to calculate the 70<sup>th<\/sup> percentile which\nmeans the number for the 70% of our population.<\/p>\n\n\n\n<p>As with quartile we will use both methods. First the\ninclusive one: We will type the name of the function followed by the percentage\nin question (70%).<\/p>\n\n\n\n<p>Then we do the same for the exclusive version: The function\nand the percentage.<\/p>\n\n\n\n<p>We notice the difference of the results they produce is\nsmall as in the quartile functions.<\/p>\n\n\n\n<p>For compatibility reasons the older Percentile function is\nstill supported at the 2016 version of Excel. <\/p>\n\n\n\n<p>The old Percentile and Quartile functions are the same as their inclusive counterparts. It is preferable to avoid using them because they might not be supported in future versions of Excel. <\/p>\n\n\n\n<p>There are close to 10 different methods for calculating\nquartiles and percentiles, which provide, most of the time, different results,\nas we have seen in our examples.<\/p>\n\n\n\n<p>There is no agreed standard way for calculating them, but\nthe exclusive method is the most commonly implemented and used.<\/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, the quartiles, of a ranked set of data values, are the three points that divide the set into four equal groups. Each group constitutes a quarter of the data.<\/p>\n","protected":false},"author":2,"featured_media":603,"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":[58,60,59,55,57,56,14],"class_list":["post-601","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-percentile","tag-percentile-exc","tag-percentile-inc","tag-quartile","tag-quartile-exc","tag-quartile-inc","tag-statistical"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/quartiles.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9H","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/601","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=601"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/601\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/603"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}