Excel Rank Top N Values Inc Tied Values with Dynamic Array Formulas

In this video, we create a dynamic ranking of top n values using Excel formulas only.

The user can select if they want to see the top three, top 5 or top eight from a list and the rankings dynamically change.

The formulas also handle the problematic scenario of tied values. Tied values are assigned the same rank, and the rankings then adjust accordingly.

Tasks such as this are made so much easier with dynamic arrays formulas such as FILTER and SEQUENCE. Both play a pivotal role in this solution. A few other friends come to help too.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[IvanCortinas_ES]

Complex case (with trick), extraordinarily explained. You are one of the brightest people explaining Excel. Thank you very much for sharing Alan!

[omarrashid]

Finally found what I was looking for. Thank you for a clear and detailed explanation

[miaquaintrelle]

Finally found what I was looking for. Thank you for a clear and detailed explanation 😊 🙏🏻

[billbandito]

Finding this video was a blessing, nice work and thank you so much!

[Ryan-zzhh]

Excellent video! Thanks for walking us through the possible errors and for explaining the overall solution clearly. Loving the new dynamic array functions.

[reibalachandran]

This video was a life saver! thanks so much! such a cleve solution!

[anisulhoquechowdhury]

Great!!!! Football world cup is knocking the door. Create a dynamic fixture where Quarter finalists/semi finalists/finalist will take position automatically after I input score in prior matches...

[GentzaBallesteros]

i can't believe this solves almost the exact same problem i was dealing with. Thanks a lot!

[rany]

Another amazing video....I am catching up on all what I missed....love it😍🤩

[elijahgastineau]

What should I do if I want the tied values to each take up a separate ranking in the table? I'm in a situation with 6 tied values and I want to make them each have their own ranking.

[sasavienne]

First *LIKE* before watching.. Thanks Alan. 👍.. Salim

[Seppe]

@Computergaga what do i do if i dont want the names bob and hiran combined but just below each other

[caspyuk]

just what i was looking for.. thx for the well produced video.. so easy to follow... what would you do if you had 2 columns of names and 2 columns of values to find the ranking of both combined ?... maybe a wee addon video to explain ?.. anyways// ill be following you now.. great stuff

[sachinrv]

That's is fabulous, I liked the trick of Length function. Clearly FILTER is versatile function. By the way I was thinking of you might use the old fashioned =IF(ROWS($E$6:E6)>$E$3, "", ROWS($E$6:E6)), but SEQUENCE does it effortlessly. Cheers :)

[wayneedmondson]

Cool trick! Thanks Alan :)) Thumbs up!!

[riddhigi]

Complex things explained in simple way, though would like to know how to add condition in this ranking computation like in this example if we want to rank it based on the region, how this can be done in dynamic way

[MasTommy]

What to do if i want to rank by REGION from your data? So there will be regions with multiple values... and must be summed first

[cgrablew]

I like the video but what if you only wanted the top x for Birmingham? Can you do a filter inside of a filter?

[lorenwilliams]

How would I do this if I have a list on multiple tabs?

[MasTommy]

How can i do that, but for multiple transactions and duplicating items?

So i have to sum it all first, and then execute your formula. Help?