Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates
Vložit
- čas přidán 2. 08. 2024
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel File: people.highline.edu/mgirvin/Ex...
Concepts:
1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers.
2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE).
In this Video:
1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges.
2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges.
3. How Come VLOOKUP will not work
4. Find Hurdle Value for Top 5 using LARGE function
5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)
You are welcome! I am happy it helps!
I am glad that you liked it!
I am glad that you like it!
Man, you probably have no idea how much this helped me out. I was on the brink of going crazy before I found this. So thanks a lot!
Tee
You are welcome!
You are welcome! I am happy to help!
Wow!!!! That is TOTALLY amazing!!! I had no idea that it could work that way.
Thanks for the great trick.
I can't wait to make a video to display your amazing trick!!
I am glad that you like it, krn14142! Yes, I changed the production for the mic because I had some "pops" in this particular video. I also produced in HD - but it took 20 times longer (literally instead of producing in 1 - 2 minutes it took 20 to 30...)
Hi Mike, I've been following your Excel for quite sometime now. My boss said I'm the Excel expert in the office, and I owed it you a lot. cos, i believe 80% of my excel skill come from watching your video. just wanna say thank you.
Absolutely love learning in this specific modular approach. You are definitely a magician with this stuff! I'm so glad I came across your channel. Greatest example of knowledge sharing! You're making such a difference. THANK YOU!
this video is beyond excellent. really helped me out on some of my projects. totally awesome. does a great job explaining it in easy follow along style.
these current formulas and the set up is perfect probably for most people. hope him or someone would please post formula in the rank column to make it: 1,1,3,4,5,5,5,8, etc...
with this added would make it complete and i believe a lot of people would really love it. the best without dispute.
I am glad that the video helps!
Ah man, this has literally saved my whole spreadsheet model. Thank you so much!
That trick was Fantastic Mike. Admire all your passion for Excel and to share this knowledge with the Crew !
You are welcome for the knowledge!
Cool! I am glad that you liked the video!
Hi, this is a really great show of how to use some of the functions! Will definitely be using this demo to help set up my data! Thanks :-))))
This guy is a great presenter. Clear speech.
Cool! I am glad that you like this video!
I Love you!!!!! I've saved so many time!! You make my day!!
Really well done! Thank you for your clarity! You rock!
You are welcome!!!
Wow. That is amazing. Thanks Mike for this EXCELlent video.
This was amazing. Great Job! One of my favorites.
I do not have a video on that particular situation. For back and forth dialog to get customized Excel solutions, try:
mrexcel [dot] com/forum
I have some basic videos on Access:
youtube [dot] com/course?list=ECB0DB785B9C9BE8A8
Great CZcams site about Access:
youtube [dot] com/user/learnaccessbycrystal
Thank you for sharing this information! Worked perfectly!!!
I do not have a handout or video on the generic rules of how to put functions together. Sorry.
Thanks!! This series is very helpful. Please keep posting these excellent excel tricks and tutorial.
Regards,,
GK
Thank you for the kind words. I always wonder what people are thinking when they dislike a video that has the sole intent of giving away free information in a reasonable way...
I have some basic videos on Access:
youtube [dot] com/course?list=ECB0DB785B9C9BE8A8
Great CZcams site about Access:
youtube [dot] com/user/learnaccessbycrystal
Man, you probably have no idea how much this helped me out too :( , I was on the brink of going crazy before I found this. So thanks a lot! i'm following now all your videos.... thanksssssssssssss
You are welcome, Abdelsalam!!!!
@@excelisfun thanks
you saved my life! THANKS!
You are welcome!
Wow! Beautiful setup with the RANK! I can't wait to see your video!
There should not be any dislike button for all videos on this channel, thank you, your videos are much appreciated and there is a request, please make videos like this on access also
You are awesome, the video is just exactly how i need it. Thank you so much!!!!
Mike
instead of select the formula manual before pressing f9 to show the result of your formula
you may select the section of the screen tip, excel will select the formula for you
example in
if(a1=b1,c1)
you don't have to select the a1=b1 manually before pressing f9
in screen tip (if(logical test,value if true,value if false)
just select logical test in the screen tip
then excel will select a1=b1 for you
you are a great teacher
I learn a lots from your videos
million thanks
Excellent video. So clear to follow and downloadable files to go along with the video... What could be better? I should've found this sooner.
Thank you so much!
I do not know. Try THE best Excel question site:
mrexcel [dot] com/forum
Mate really appreciated, you help me a lot buddy. Thank you very much.
Dude, you just saved me hours of my life. Thanks for this, you're a legend :D
Wow this video saved my day. thank you!
Great thanks ! And great formula !
AWESOME!!! Thank you so much. I have been trying to figure this out for like a year :D
The formulas are finished. I modified it, to show the ranks not in order 1,2,3,4,5,6 but in 1,2,2,4,5,5 if there are some ties.
I used a time list and added an additional Rank.eq in Column C
C6: =Rank.eq(a6;$a$6:$a$12;1)
D6: =if(rows($d$6:d6)
Great video! saves me whole day trying to figure out how to extract top 10 numbers with names from a pivot table
Thank a lot , for your brilliant and lucid explanation , well advanced tricks made very easy for all with perfect presentation and with its download files to learn , practice and use ,
you are a unique mentor ! ! !
Glad to help, vipul!!!!
You are too brilliant, no word to explain you how much you helped me, thanks dear
Glad the videos help, Aashish!!! Thank you for your support with your comment, Thumbs Up and Sub : )
I feel he is a great Teacher
very easy to understand
Keep it up
Thanks a lot
give us about more about Access
this is great! thank you so much for your help. Do you think you could also show us how to improve this formula with two variables for small function? I am using it for the Risk register to pull out Top 5 risks. However, I need to apply an additional condition that pulls out only Top 5 risks which have the status "open".
You are amazing teacher. I admire you , you make me to love ,to woik in excel .. Thank you
Great job, solved many problems :-)
What an amazing teacher you are!
Glad you like it, Robert! Thanks for your support : )
Amazing video... So helpful.. Thanks a lot Mike
Glad I can help : )
This is amazing, i want to know if you have 5 times of the same manager with all different visit can he calculate from each manager and after rank them ?
Hi, and thanks for a great video, it was a huge help for me. An additional question, is it possible in any way to add a secondary criteria, when making the top 5? Fx. when shooting clay targets. 20 hits in 20 shots. Then 20/21, 20/22, 19/20 and 19/21. That's the correct order, obviously 20/20 is better than 20/21. How can I make excel sort it out for me?
Regards, Anders Bruun
I've volunteered to be the treasurer of a local organization and have been busy building a spreadsheet to help me track our finances. I think of a feature I'd like to add but have no idea how to implement it but all I have to do is look through your videos and I'll find the answer. Thanks a lot for your help.
thank you so much! this really help me to analyze data much much faster!
Glad this helps, Ammar!!!
Thank you sir today I learned new things from you
you are amizing ...this is summery for hundred of your vedios....thanks so much
Your work is excellence
You are awesome
excellent...thanks man..
amazing trick. now i can make pareto charts fast and easy
Thanks a lot for your comments on my CZcams channel.
Now I'm looking for a way to show the correct ranks without column C. Still waiting for your next video, Andreas.
i did it ,it works and i dont know how it work but it gives me what i want
Grate formula... Tq
Thank you for the links
Is there a way to extract top values for displayed cells only? Amazing lesson btw, thanks!
Hi, thanks for great videos. what shall I do more to have a formula as mentioned in this video but with new column in the data like department, for example the top 5 visits from a specific department
Welcome Owsome bro just keep it up 👍👍👍👍 fabulous
: ) : ) : ) : )
big up, v helpful
Thanks a lot. It helped me very much
You are welcome, a lot! Thanks for your support with your comment, thumbs up and Sub : )
Hi! Thank you so much! I have a question: Is it possible to use the countif function for a set of columns with defined names and try to get the result by row? . Something like: countif ("column1name" and "column2name", and "column3name", etc) have more than one number in each row. Thank you!!!!
Thanks for this tutorial.. I've got a question though. I'm having a difficult time logically putting together & branching Excel functions/formulas together.. Do you have any videos or PDFs where you basically go through the fundamentals of this? Right now, I'm at the point where I'm only really able to put multiple functions together by memory, and I'd really like to get a stronger understanding..
Wow, thanks a lot. That's very helpful.
You are very welcome, Kenneth!
@@excelisfun if i want D6:D10 to show 1,2,3,4,4 instead of 1,2,3,4,5. How could I do that? thanks
Thank you so much for this sir this is really great..
You are welcome, jhiechel1!!!
You rule man!!
That is so funny, this video is the video I am releasing tomorrow, Oct 4, 2019 as my Flash Back Friday. It is an oldie but a goodie...
Yes, you can make a re-make of it in German!
Excelisfunner says hi to you!
Great wrk 👍👍 thanks for your vedio
You are welcome, s.kishore!!!
Thank you
I am trying to consume this very valuable knowledge , and scratching my head too as it's not easily understandable for novice like me
Hi sir in the case of the tie can we add criteria also for the ranking?Thanks
It does seem weird, but it is the syntax that the COUNTIF function requires in this situation.
nice sir 👍🏻👍🏻👍🏻👍🏻👍🏻
Thanks for the great tutorial (again). I would like to produce a german remake of it.
Greetings from Germany - also to ExcelIsFunner
Thanx
Mike, wonderful video!.. I was wondering if you could maybe try a video of adding an additional criteria and then extract based on this additional constraint? For example, lets select the top 5 but eliminate the "Max" because it meets the criteria of an "outlier" ... resulting in an extract of only 4.... Scratching my head over this... but still trying to figure this out...
I'm needing some help. I'm using Excel 2016 to create a volleyball round robin spreadsheet and I'm needing to get the Top 5 players in order, everything seems fine, but the VLookup function isn't returning the name of the players. I don't know what I'm doing wrong...please and thank you!
Is there a way to add date criteria also, I want to show two lists in my dashboard report, Top 10 last month and Top 10 last year. I refresh my data base table of transactions several times per month. p.s. I've learned fantastic tips and tricks from you over the years
Thank you thank you thank you for your help with this!!!!
You are welcome, welcome, welcome, J. B.!!! Thanks for your support on each vid with your comment, Thumbs Up and of course your Sub : )
@@excelisfun In your example the area of return is the same size as the dataset whereas my area of return is much smaller. I am getting false outcomes and am wondering if that could be the cause. Any ideas?
@@excelisfun solved it
Great video Sir, I am trying return value matching different columns but row function doesn't help
On a different note, what if there are 3, 4 or 5 names or data that are the same instead of 2 same names as in the example? How would it be coded? Do you have a video of a variation of this??
What to do when in top ten more than 1 duplicate value
Thank you so much this really helps me listing all the names with the tie marks. But can you please advise how do we do if we also want to rank the name of the tie marks based on other condition. For the example in your case, Moji and Hafiz have the same marks. can i ranking Moji and Hafiz based on other condition such as other marks? Thank you.
Thanks u safe my life.
Glad the video helps, Niti!!!!
Thank you for this tutorial. One more question I am using this example in a combination with week numbers but is not working do you have any idea?
I enter in B4 week nr and in the C5 to C12 are week numbers example 1,1,1,3,3,4,4
when I enter 1 in B4 excel should show top 5 in this weeks and the same with other numbers. thank you for your feedback.
That is too bad that you can't install 1013. But no worries, Excel 2010 or 2007 still are TOTALLY amazing!!!
Hi this is a great video, but I do have a quick question:
How would you go about filtering via formula top 2 managers that contain names starting with the letter M?
This is hypothetical and relative to what I'm trying to do. I'm trying to pull out the top performers of 2012, when 2011 is also listed in the same range. I'm having trouble leaving the 2011 top performers out and can't solve it on my own. All help is appreciated!
Ryan
You do not want to use IFERROR rather then IF. Why? Because with an alternative logical test and the IF, the formula will not have to run the big array formula in every cell; with IFERROR the formula must run the whole formula in every cell. Although for small data sets, the calculation time saved is not noticeable, for large sets it is dramatic. It is efficient to use IF instead of IFERROR . Here is a video:
Excel Magic Trick 1029: DON'T Use IFERROR, Use IF and Logical Test If Possible...
hello, i've been trying to figure out, if i have a 15 names in the cells for example from cell4 to cell19, how can i manage to highlight the name which is more repeated in the cells, thank you in advance for the reply.
Great video! It helped me with a leaderboard for a baseball stat.
I have run into one tiny problem. How do I create a restriction with what you're doing at roughly the 8:00 mark to make a minimum appearance requirement? If, say, a pitcher needs 15 appearances to qualify for a percentage stat? With what I'm trying, the function returns the first guy it sees with the top-ranking percentage, despite the fact that he has only one appearance. Thank you in advance!