{"id":337,"date":"2019-04-04T15:18:50","date_gmt":"2019-04-04T12:18:50","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=337"},"modified":"2019-05-06T12:29:08","modified_gmt":"2019-05-06T09:29:08","slug":"how-to-user-the-large-small-functions","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-user-the-large-small-functions\/","title":{"rendered":"How to user the LARGE and SMALL Functions"},"content":{"rendered":"\n<p>The need to determine the maximum and the minimum values of\na range of cells, is common to almost all types of excel projects.<\/p>\n\n\n\n<p>But in statistics the <strong><a rel=\"noreferrer noopener\" aria-label=\"MIN (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-min-function\/\" target=\"_blank\">MIN<\/a><\/strong> and <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-max-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"MAX (opens in a new tab)\">MAX<\/a><\/strong> functions are not enough. The need to calculate, for example, the third largest or smallest value of a range of cells, is very common.<\/p>\n\n\n\n<p>Luckily excel provides with functions to do just that.<\/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\/0df2WIKZLO0?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:100px\" 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\/largesmall.xlsx\">large,small<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/largesmall.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 <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-large-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"LARGE (opens in a new tab)\">LARGE<\/a><\/strong> function which returns the k\u2019th largest value from an array of numeric values and the <a rel=\"noreferrer noopener\" aria-label=\"SMALL (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-small-function\/\" target=\"_blank\">SMALL<\/a> function which returns the k\u2019th smallest value from an array of numeric values.<\/p>\n\n\n\n<p>In the current worksheet we have already calculated the\nthird largest and smallest values using the functions.<\/p>\n\n\n\n<p>Their syntax is straightforward. We select the range of\ncells and then the index of the largest or smallest value we need.<\/p>\n\n\n\n<p>We should make sure that all the numbers in the cell range\nare numeric values and not text representations of numbers. If this is the case\nthe functions might produce the wrong result since they ignore text values. <\/p>\n\n\n\n<p>They might even throw a NUM error if there are no numeric\nvalues or the actual numeric values are less than the index supplied.<\/p>\n\n\n\n<p>In this case we have mistakenly set the value of cell B17 as a text. By fixing our error, we see that the 3<sup>rd<\/sup> largest value is now correctly calculated.<\/p>\n\n\n\n<p>Let\u2019s calculate the 10<sup>th<\/sup> largest and smallest\nvalues now in cells F10 and f11 respectively.<\/p>\n\n\n\n<p>First we type the name of the function followed by the cell\nrange, and then the number 10. That gives us the 10<sup>th<\/sup> largest value\nin our data set.<\/p>\n\n\n\n<p>We follow the same procedure for the 10<sup>th<\/sup>\nsmallest. The cell range and 10.<\/p>\n\n\n\n<p>Pretty easy, isn\u2019t it?<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>The need to determine the maximum and the minimum values of a range of cells, is common to almost all types of excel projects.<br \/>\nBut in statistics the MIN and MAX functions are not enough. The need to calculate, for example, the third largest or smallest value of a range of cells, is very common.<\/p>\n","protected":false},"author":2,"featured_media":408,"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":[26,27,14],"class_list":["post-337","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-large","tag-small","tag-statistical"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/large-small.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-5r","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/337","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=337"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/337\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/408"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}