Excel Rank Top N Values Inc Tied Values with Dynamic Array Formulas
Vložit
- čas přidán 11. 07. 2024
- 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.
You can download the file to follow along here - www.computergaga.com/_excel/f...
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - Jak na to + styl
Complex case (with trick), extraordinarily explained. You are one of the brightest people explaining Excel. Thank you very much for sharing Alan!
Thank you very much, Iván. I am happy it helps.
Excellent video! Thanks for walking us through the possible errors and for explaining the overall solution clearly. Loving the new dynamic array functions.
Thank you Ryan. Yes, they are amazing 😜
Finally found what I was looking for. Thank you for a clear and detailed explanation 😊 🙏🏻
You're welcome, Mia. Thank you.
Finding this video was a blessing, nice work and thank you so much!
You're very welcome! Thank you for your comments.
Finally found what I was looking for. Thank you for a clear and detailed explanation
You're very welcome. Happy to help.
Another amazing video....I am catching up on all what I missed....love it😍🤩
Thank you.
Great tricks! Thumbs up!
Thank you.
Cool trick! Thanks Alan :)) Thumbs up!!
Thanks, Wayne.
Beautiful video!!!!!! Thankyou!
My pleasure!
First *LIKE* before watching.. Thanks Alan. 👍.. Salim
Thank you Salim 😀
This video was a life saver! thanks so much! such a cleve solution!
You're welcome! Thank you.
Thank you very much for this.
You're very welcome!
Brilliant!
Thank you 😊
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
Super duper awsome
Thank you Rakesh.
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.
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 :)
Thank you Sachin. It is a lot of fun seeing what these new functions can do. And how much easier they make Excel tasks that were once complicated.
@Computergaga what do i do if i dont want the names bob and hiran combined but just below each other
i can't believe this solves almost the exact same problem i was dealing with. Thanks a lot!
What can be done when the values are in more than 1 column, but the selected column to extract the top values, depend on the day of the week (the columns headers are the days of the week)? Maybe telling excel which column to pick? Thanks in advanceee!
You're welcome. Thank you.
Sure. This can be done. You can tell Excel what column to pick from a cell value if that works for you. Entering the weekday you are interested in into a cell. The INDIRECT function or a lookup such as MATCH (depending on your exact setup) would then be used to fetch the required column. I have a video here on a similar thing with the SUMIF function - czcams.com/video/OM4MAaQ4oN0/video.html
@@Computergaga Thanks for the response! I checked some of your videos and they are very helpful. I would like to make one last question, what if i want the function FILTER (or any other if this request can't be fulfilled with the FILTER function) to differentiate between 2 names with the same value (Bob and Hiran in this case) and list them downward as different elements? cause i need to treat them as different instances. Thanks a lot and have a good day!
There are a variety of techniques. I have a video here using COUNTIF for unique rankings.
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
Hello,
Do you know how I can get the formula to work in googlesheets? I tried manually entering your formula and came with issues, I tried downloading your excel and importing it into google sheets and after the upload/conversion, the formula comes out with #ERROR. Is google sheets limited in use of this function?
I like the video but what if you only wanted the top x for Birmingham? Can you do a filter inside of a filter?
Oh absolutely. The criteria section would be this - (team[Region]="Birmingham")*(team[Value]=LARGE(team[Value],E6))
You can check out more about the FILTER function in my video - czcams.com/video/kdl3mNEyIRE/video.html
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...
Absolutely!!
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
Thanks, Girish. I have a similar video for conditional rankings - czcams.com/video/g-L0uvVcEoM/video.html
Thanks Computergaga❤️
Hi...What if I can't find the FILTER function on my Excel? Is this an add-on?
Hi Ann, the FILTER function is only available in Excel 365.
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?
I would probably create an intermediary table with the items and SUMIFS. Then run this formula off that range.
when you select the row it puts "team[value]" in the function how do you do this? when I select a row for an array it just gives me, for example C:C
I know it's been a while, but that's because the first array (the one from which you get the ranking) is an Excel object called a Table and like Named Ranges, they have their own syntax for references. Anyway, I reply in case someone else has the same question.
Thank you again !. download link is dead :( Could you fix it ?
My pleasure. Download link is fixed 👍 This is caused by my new website being installed a few weeks ago and a couple of directories have changed.
How would I do this if I have a list on multiple tabs?
It depends exactly on what you mean by this, but if you're referring to the data source, they would need to be aggregated before we can rank them in a list.
If you're after multiple ranking lists, then rinse and repeat the same steps for each tab.
what can I do if I have no FILTER function?
There are various other ways to RANK. Another option is to use the RANK.EQ function with COUNTIFS to ensure unique rankings, video here - czcams.com/video/QFdsGGlSmrU/video.html
A lookup formula such as VLOOKUP can then order them with whatever additional detail you want.
You can do this with one helper column by ranking the values without skipping a rank position. The formula is for this example MATCH(team[@Value],SORT(UNIQUE(team[Value]),,-1),0)
You can also do this without helper columns by first wrapping you value array in the UNIQUE function and do the LARGE function. Then TRANSPOSE the FILTER function and apply the TEXTJOIN function.
4:18 # SPILL Error
7:30 New Rank
8:55 Rank Helper Column