Rick from New Jersey asks if it is possible to use the new FILTER function to do a Top Five filter. This is one where it sounded easy, but as we dove into what was really needed, it became more complex. Stick around to the 4 minute mark where I am forced back into Ctrl+Shift+Enter land.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2304.
Can you Filter to the Top Five?
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
While I've been hearing that the new dynamic arrays have been making it out through Office 365.
It's now out to the monthly channel an which means a lot more people are getting access to it.
And one of the people in my seminar, Rick, was happy to have the new functions and said Alright, I'm trying to do something particular here.
Can I use the FILTER function, but to do a top five filter, I said ha, that's interesting. I've never thought about that.
I guess the first step here is to figure out what the largest value is, not the largest value. That would be easy. That would be MAX.
But if we want to find the 5th largest value in the data set so =LARGE(, the data, comma 5 will give us the 5th largest value, and then we could use that as the criteria.
For the include argument of the filter, so filter all the data in A2:D43.
We only want where D2 to D43 is greater than or equal to this helper cell.
Up here, and that seems to work, gives us the top five.
Now if you don't want to do it in two formulas, you can just wrap them: put the LARGE function right inside.
So many times I get a question from someone I send the answer out and they're like.
Oh no, that's not exactly what I want to do. It's more complicated than that.
I want to.
Filter to the East Region 1st and then find the top five just from the East.
Alright, so here's where we enter which region were looking for, and then the first question is what is the 5th largest value in the East?
Check out this little formula here.
We say if B2 to B43 - if the regions are equal to the selected region then give me the value from D2 to D43.
Otherwise give me a 0 send that whole thing into the LARGE function.
And again asked for the 5th, so that gives us 8219 for the whole data set.
8569 was the 5th largest value, but for the East region it's 8219.
You'll see if we change to Central 6167, West 6141, so that's updating on the fly and then to do 2 conditions inside the filter.
And I'm pretty sure I've covered this before you put each condition in parentheses and multiply them together.
So we need a true times true for that to be true and we get our top five beautiful right again to do that without the helper cell the formula starts to get a little bit ugly. Little bit hard to understand.
D2 to D43 is greater than equal to the large of the if and then multiply that by be to to be 43 equal to the region. That's all great alright.
A couple weeks went by and everyone was happy but then then we came up with a situation where there is a tie.
What if there is a tie for fifth place?
So her formula saying you know basically want everything above 8219, so I fake the data here.
I went down here and took the 6th largest item and made it be 8219.
so I thought I was asking for the top five, but in fact I'm getting 6 alright.
So what if we want to resolve this and really only return 5?
So the beautiful thing about Excel even with these dynamic arrays is there's multiple waves to solve every problem I said Alright, Let's do this first step.
Let's find just the East region record, so that's simple enough.
Filter A2 to D43 and then the include is where the region is equal to the selected region like that?
OK, then I said all right now that we have just the East Region Records.
Let's take that formula and sort it. So the sort I want to sort the filter.
Which column? I want to sort by want to sort by the 4th column.
And which order? Descending. So minus one.
And that's going to give me just the East region but this time. Sorted high to low, all right now.
Here's an awesome little trick when dynamic arrays came out, it was about almost a year and four or five months ago.
Joe McDaid on the Excel team said, oh, you'll never have to press Control Shift Enter again. Not so fast there.
Joe, Control+Shift+Enter still has a use.
So what I'm gonna do is I'm going to copy this formula to the clipboard. Control C.
Get rid of the formula and if I want to limit these results to just five rows, I'm going to select the five rows and the four columns like that type the formula. Paste it in there and then press control+shift+enter.
That will force them to only give me 5 results like that.
Alright, so this is great.
Great news that the dynamic arrays have made it out to the monthly channel.
The last hurdle, of course is the semi-annual channel on that'll happen later this year (July 2020?) I know a lot of you already downloaded the book Excel Dynamic Arrays Straight to the Point. I've updated that book.
Now that we've kind of reached general availability, the 2nd edition.
It's not free anymore.
$3 click that "i" in the top right hand corner.
Hey, if you like the tips in this video please subscribe and ring that Bell.
Feel free to post any questions or comments in the comment section below.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Can you Filter to the Top Five?
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
While I've been hearing that the new dynamic arrays have been making it out through Office 365.
It's now out to the monthly channel an which means a lot more people are getting access to it.
And one of the people in my seminar, Rick, was happy to have the new functions and said Alright, I'm trying to do something particular here.
Can I use the FILTER function, but to do a top five filter, I said ha, that's interesting. I've never thought about that.
I guess the first step here is to figure out what the largest value is, not the largest value. That would be easy. That would be MAX.
But if we want to find the 5th largest value in the data set so =LARGE(, the data, comma 5 will give us the 5th largest value, and then we could use that as the criteria.
For the include argument of the filter, so filter all the data in A2:D43.
We only want where D2 to D43 is greater than or equal to this helper cell.
Up here, and that seems to work, gives us the top five.
Now if you don't want to do it in two formulas, you can just wrap them: put the LARGE function right inside.
So many times I get a question from someone I send the answer out and they're like.
Oh no, that's not exactly what I want to do. It's more complicated than that.
I want to.
Filter to the East Region 1st and then find the top five just from the East.
Alright, so here's where we enter which region were looking for, and then the first question is what is the 5th largest value in the East?
Check out this little formula here.
We say if B2 to B43 - if the regions are equal to the selected region then give me the value from D2 to D43.
Otherwise give me a 0 send that whole thing into the LARGE function.
And again asked for the 5th, so that gives us 8219 for the whole data set.
8569 was the 5th largest value, but for the East region it's 8219.
You'll see if we change to Central 6167, West 6141, so that's updating on the fly and then to do 2 conditions inside the filter.
And I'm pretty sure I've covered this before you put each condition in parentheses and multiply them together.
So we need a true times true for that to be true and we get our top five beautiful right again to do that without the helper cell the formula starts to get a little bit ugly. Little bit hard to understand.
D2 to D43 is greater than equal to the large of the if and then multiply that by be to to be 43 equal to the region. That's all great alright.
A couple weeks went by and everyone was happy but then then we came up with a situation where there is a tie.
What if there is a tie for fifth place?
So her formula saying you know basically want everything above 8219, so I fake the data here.
I went down here and took the 6th largest item and made it be 8219.
so I thought I was asking for the top five, but in fact I'm getting 6 alright.
So what if we want to resolve this and really only return 5?
So the beautiful thing about Excel even with these dynamic arrays is there's multiple waves to solve every problem I said Alright, Let's do this first step.
Let's find just the East region record, so that's simple enough.
Filter A2 to D43 and then the include is where the region is equal to the selected region like that?
OK, then I said all right now that we have just the East Region Records.
Let's take that formula and sort it. So the sort I want to sort the filter.
Which column? I want to sort by want to sort by the 4th column.
And which order? Descending. So minus one.
And that's going to give me just the East region but this time. Sorted high to low, all right now.
Here's an awesome little trick when dynamic arrays came out, it was about almost a year and four or five months ago.
Joe McDaid on the Excel team said, oh, you'll never have to press Control Shift Enter again. Not so fast there.
Joe, Control+Shift+Enter still has a use.
So what I'm gonna do is I'm going to copy this formula to the clipboard. Control C.
Get rid of the formula and if I want to limit these results to just five rows, I'm going to select the five rows and the four columns like that type the formula. Paste it in there and then press control+shift+enter.
That will force them to only give me 5 results like that.
Alright, so this is great.
Great news that the dynamic arrays have made it out to the monthly channel.
The last hurdle, of course is the semi-annual channel on that'll happen later this year (July 2020?) I know a lot of you already downloaded the book Excel Dynamic Arrays Straight to the Point. I've updated that book.
Now that we've kind of reached general availability, the 2nd edition.
It's not free anymore.
$3 click that "i" in the top right hand corner.
Hey, if you like the tips in this video please subscribe and ring that Bell.
Feel free to post any questions or comments in the comment section below.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
