Hi, this is Wayne again with a topic “Using the Excel LARGE and SMALL Functions”.
In this Excel video, I will show you how to use a formula to calculate the second third, fourth, fifth highest of a range of numbers, and you can also do the same thing with the lowest second lowest, third, lowest, Etc. Let’S get started so here I have a list of fictional students with some made up test scores and I would like Excel to figure out the highest score and the second and third highest score, as well as the lowest three scores and many Excel users already know How to find the highest number generally, what you would do is you would use the max function to find the maximum or highest number in a range of cells, and I’ve already done that here. But in this article I want to focus on how to find the second highest and third highest. It doesn’t seem like. The max function is what we want when searching for the second and third highest Etc. So let me show you what to use. Instead, I’m going to click here on Cell C4 and I’ll type equals and instead of Mac, I’m going to type large.
The large function left parenthesis next Excel is expecting an array. An array is just a range of cells, so I could just click and drag on the test, scores so H2 through h21 and I’ll release the mouse button, and because this data is in a table, you’ll notice that Excel puts in the table and the column title. If this weren’t a table, it would just show the range, it would literally say – H2 colon h21 with the colon meaning through so H2 through h21, but I do have it in a table. So it’s got the table name and the column name. Next I’ll put in a comma and here Excel is expecting K. What does that mean? Basically, what rank am I looking for rank number two, three, four five: what is it that I’m looking for in this case, I’m looking for the second highest, so I’ll put two.
I should put in my right parenthesis: tap enter on the keyboard and this second highest score is produced in cell C4. Let’S check to see, if that’s right did someone get a score of 99? Yes, so that is the second highest score. Now, let’s calculate the third highest, I click on C5 equals large left parenthesis this time, instead of clicking and dragging I’m just going to type in the cell references so H2 through which is indicated by putting in a colon h21 comma.
Now the K, what is the rank that I’m looking for three, the third highest right parenthesis, tap, enter and the third highest score? According to this is 95, it looks like that is correct. Now, let’s try it with the lowest scores for the very lowest score. I’Ve already used the Min function, which is basically the opposite of the max function. Again, if you don’t already know how to use the Min function, please watch my other Excel functions tutorials, but here I’m going to figure out the second and third third lowest scores. So here on Cell K4, I’ll type equals and I’ll type small left parenthesis.
What’S the array? Well, it’s that same exact array, but this time instead of clicking and dragging or typing the cell reference, I’m just going to click on column H. This selects the entire column and look it automatically puts in h, colon H, I’ll put in my comma Now. What do I put for the k for the rank I’ll put in the number two for the second lowest? I should put in my right parenthesis, but you know what, in this case, you don’t really have to so I’ll tap enter on the keyboard. The second lowest score according to excel is 61..
Let’S do the same thing with third lowest equals small left parenthesis. My array again I’ll just click on H in some ways it’s the fastest way I’ll put in my comma – and I want this to be the third lowest score tap enter and there we go now. It is possible that you’ll have two numbers that are are the same.
What if Carissa here also got 99 percent? If I tap enter on the keyboard, you’ll see that both of the scores of 99 are counted here, so they tie for second place, but they both take up a rank. Second and third, I think that’s good. It’S also important to realize that, instead of using Max and Min, I could have used large and small.
So here on Cell C3, I’m going to go up to the formula bar and I’m just going to erase what’s there and replace it with equals. Large left parenthesis I’ll select my column, H, comma and one tap enter on the keyboard and I get the same results 100. But this time I’ve used the large function instead of the max function. We can do the same thing with Min I’ll, just switch it to small and I’ll click here inside my formula after the range I’ll put in a comma and a number one and tap enter so I’ve converted the Max and Min formulas into large and small formulas. In some ways, large and small are more useful than Max and Min, but it’s important to be aware of all four of those functions and please keep in mind that large and small work for much more than second and third, I could create a formula here that Says equals large left parenthesis, select, column, H, comma, and I want the 18th score. Tap enter it’s 64.
and this could go as high as I needed to let’s say: 108 now I don’t have 108 students in this list. When I tap enter, look what happens? There is no 108th highest score in this range, so the formula produces an error message, but if I did have enough students and data in the spreadsheet, it would produce the 108th highest score thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel, consider clicking the thanks button below the video where you could support me through my patreon account or by buying Channel merch and you’ll, see information about those options in the description below the video. Speaking of patreon, I’ve got to say a big thank you to my five dollar patreon supporters.
Thank you. So much for all you do to support my channel so that I can continue to make these Excel tutorials and many other educational videos. Thank you.
.