From the course: Excel: Advanced Formulas and Functions

LARGE and SMALL

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

LARGE and SMALL

“

- [Instructor] In Excel, there is max and min to give you the max number or the minimum number in an array. But what happens if you don't want just the top one or the bottom one, but you'd like the second value, the fifth value, et cetera? That's what we're going to do here. We're going to do large and small. We've got data about these different colors and the various media. So we got a column for gel. So eggshell gel, 30 was sold. Mold blue oil, 84 was sold. Now we just need some quick analysis. We don't care about the colors, we just want to look at the values. The top one are the max equals large. Double click. What array? And notice we don't have a single column or a single row. We really have an array. So I'm going to highlight here. And then F4 for the absolute reference. Comma K, that's the number that I want from the array. I want the largest one. Enter, 84. I'm going to go back and drag this down. And notice we have two 84s in our dataset, and large is showing 84 as both the first and second values. Now, let's do small equals small. Double click. Want the array. Lock that down with F4. Comma, we want the first smallest. And that's negative three. Go back, double click. The fifth smallest value in our dataset is five. Now, it's important for you to notice small ignored the blank cells. You see we got negative three. The second lowest number is zero. And even though we have multiple blank cells, they are not in our list of lowest ranking values. See, this is a good thing because we don't know if the blank is supposed to be zero or if we're still waiting for the data, or if there were none to sell. Those are all the possibilities for what these blank cells can mean. But let me go ahead. Espresso acrylic, oh, that was zero. We had 20 on the shelf and sold none. Ah, see, now we've got two zeros as the second smallest and the third smallest values in our dataset. And there you go, large and small.

Contents