Hi, this is Wayne again with a topic “Excel’s LARGE Function and SMALL Function”.
In this article, i will highlight, for you, two powerful excel functions and they are the large function and the small function and for some reason, these are lesser known functions, even though they’re more powerful and better in some ways than other more common functions. And you can see here the spreadsheet that i’m going to use for this. Video is a list of student names and test scores, and this is all hypothetical, not real students. So let’s say i’m a teacher and i would like to take a look at these test. Scores – and maybe i have not 20 students like are listed here, but maybe i have 50 students or 100 students, and i would like to quickly scan and find the highest score and also the lowest score, and what most people would do.
If you have some experience with excel is you would go to the cell where you want to display the highest number type equals max and then the left parenthesis and then simply select the range that you would like to test. So i want to test the scores to see which one is the highest score, so i could click and drag to highlight them, or even just click on column, g to select the entire column and then i’ll put the end. Parenthesis tap enter on the keyboard, so that returns the highest score of a hundred, and is that true, yes, it is this student here got a score of a hundred. Now, let’s go to lowest score, most people would type equals min left parenthesis again select the range right parenthesis tap enter the low score is 58.. So that’s fine, but what if, as a teacher, i want to highlight the top three scores and maybe the bottom three scores well, there are functions in excel that are more powerful than max and min and let’s use those so here for top scores.
I’M going to use the large function to do that. I just select the cell, where i’d like to display the first place score and i tap equals large, left parenthesis and then i’ll just select the range that i would like to test. So i’ll select all of column g, but what makes large more powerful than max is that i can then put a comma and type in a number, and this number will determine which of the scores is displayed.
If i put the number 1 it will list the number one largest number, if i put two, it will display the second largest number, three, the third largest five, the fifth largest etc. So i’ll put one for the largest score, the highest score, close parenthesis and then tap enter on the keyboard. It returns the number 100, which is accurate, and it’s the same as we got with max but now watch what i can do.
I could type in the same formula again, but this time here in cell j3 and then instead i could put a 2. the easiest quickest way to do. That is to simply copy this formula down. So i’m going to use the auto fill handle if you’re not familiar with the autofill handle. You should watch my videos on that topic, but using that handle i just clicked and dragged and it copied the formula a couple of times now i can double click on that formula and change the one to a two and then just tap, enter on the keyboard And now it returns the second highest score. We can look to see if that’s correct and it is shan got the second highest score 99, and now i can double click to alter this formula, or sometimes it’s better, just to select the cell and then go up here to the formula bar and make the Changes there sometimes that’s a little safer, so i change the one to a three in this case in order to display the third highest score. I tap enter on the keyboard and there we have the top three scores: 100, 99 and 95..
Let’S do the same thing now, but with the lowest scores in j8, i type equals i’ll type. Small left parenthesis. Next i select the range to be tested so all of column, g and then put a comma and now do.
I want the number one smallest number. If so i put a one. If i want the second smallest, i put a two third, smallest, three fifth, smallest five, etc. So i’ll just do a one. I want the smallest number right parenthesis tap enter and we already know that that’s correct 58 is the smallest number i’ll use the autofill handle to copy this down. But i do need to change the number from 1 in this case to 2 and in this case to 3. now, let’s double check to make sure this worked, you can see.
58 is displayed twice as the lowest score and the second lowest score. Is that really accurate? Well, gustavo got 58 and elvin also got 58, so that does appear to be correct. Third lowest score, 61 and lucretia did get 61..
So if you like max and min those are two useful functions, but you may find yourself using large and small much more often now that you know how to use them properly. They really are better because they allow you to easily pull out more information than just. What’S the absolute highest and what’s the absolute lowest thanks for watching this video, if you found it to be helpful, please like follow and subscribe and when you do subscribe, click the bell and you’ll be notified. Whenever i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll find a link to that in the description below .