Excel Dynamic array Video#8:Get top N values, dynamic range & draw dynamic chart - Filter Function

NDAF08
Hello and welcome to a new video discussing dynamic array functions, video number 8
No need to remind you that dynamic array functions are working only with office 365, if you are using any other version of the excel, you won’t be able to practice the exercise in this video
In this video we will continue with filter function, we are going to see how to extract the to N valve from a table using filter function , then create a dynamic range based on the output of the spilled array, draw a dynamic chart using this dynamic range and also create a dynamic title for the chart
While working on the example we are going to discuss some other functions beside filter function such as:
Larger , Index, and Sort
Use large, filter and sort to extract top N values: 1:32
Named ranges based on a spilled array (using: #): 9:12
Draw a chart based a dynamic range 10:00
Dynamic chart title: 11:42

To download the excel file:

To watch the full playlist

To subscribe to the channel:

To follow on Facebook:

To follow on LinkedIn:

[abdulbasitbello]

This is one of the best case-study videos on dynamic arrays in excel i have ever seen. You explained it so well and you solved my problem in less than 15 minutes!! Please keep up the good work. You deserve more praise than you are getting. The excel community loves your content!

[teoxengineer]

Wonderful presentation. Thank you so much.
Alternatively, I have solved this formula by using RANK.EQ formula inside Big formula and I have combined Filter&Sequence&Rank.Eq and Big formula at the same time and reached the same result as shown below:

Main formula is
It gives us rank sequence values smaller than Cell G3.
Also,
We can use AGGREGATE function in order to fing big 3 or more values by using big sub-function inside AGGREGATE.
The third one is Filter&Sequence&Rank.Eq and Big functions combination as given below: