Extract Top 5 Values with Names with Dynamic Array Formulas & Conditional Formatting - EMT 1610
ฝัง
- เผยแพร่เมื่อ 13 ต.ค. 2024
- Download Excel File: excelisfun.net...
Learn how to extract top five scores and names of people with the score, including when there are ties or duplicates. Learn about Office 365 Excel Dynamic Array Functions like FILTER and SORT, lean about how the new Excel Calculation Engine Spills the results so we do not have to copy formulas, and learn about how to add Conditional Formatting to Spilled Results.
That conditional formatting trick is really cool!
Cool like the other side of the pillow!!!!
Of course this task is much easier with DA!! Great Video
You are right about the magnitudes easier...
Hi Mike.. thanks for the DA solution. I love old school array formulas for the fun and creativity required to make them deliver the answer, but DAs sure make it easy and save time. Looking forward to their release.. I hope no later than year end? Thanks for the great cond. formatting trick to handle the dynamic result. Will use that right away. Thumbs up!
We still have years to come until everyone is one Office 365, so we still have a few years left with building Old School stuff. But if we Excel people will still be working for years to come build solutions, boy the future looks efficient and easy : )
You're smart Mike the conditional formatting trick was absolutely geat!👍👍👍
Smart at having fun ; )
Thank you very much !!!! Finding the Top 5 values on a pivot table that you can't reference is such a pain !!!!! With your video, I put an end to long dozens of minutes trying to find a solution....
Thank you Mike so great, and conditional formatting is always cool. Have a great day Mike 🤗
Yes, Conditional Formatting and Dynamic sort of go together well : )
Great very neat explanation sir🙏🙏
You are welcome!!!!
SORT&FILTER is amazing. Thanks Mike!
Mike, before I got to the Conditional Formatting section I was trying to figure out how to do it myself (I love the technique which I loved elsewhere on your channel). I found that a slightly simpler formula that worked is
=NOT(I4="")
I mention it because I was surprised that it worked for the spilled cells!
Thanks Mike for this EXCELlent video.
You are welcome, Syed : )
Thank you for the conditional formatting trick Mike, very very useful! 🙂
You are welcome, Paul! It is an important trick for these new and amazing Spilled Arrays : )
Great Conditional formatting trick, great video, Thanks
You are welcome, Ogwal! Dynamic Arrays and COnditional Formatting go together well!!!!
Great video Mike. This is also applicable to Google sheets (for all of us with no access to the "Insider Edition")
Google Sheets!!!!!
Thanx
Glad you like this, Ali : ) : ) : )
Thanks Mike! Love it
Glad you love it, Chris!!!!!
Very interessting video! Thanks Mike!
You are welcome, Molnify!!!
@@excelisfun :D thank you sir!
Very Interesting
Thanks, Phone Excel Time!
Hi, great tip! im wondering if there is a similar method to find the medium 10 numbers as well?
Thanks mike. Always the best. :) :)
You are welcome, John Borg : )
Thanks Mikeeee Waiting for the new calculating engine :-)
You aer welcome, Mohamed!!! I am waiting too - becasue when it comes out I can create a new class that teaches Excel from the beginning with the "New" Excel : )
Great Video Mike, however, what if 22 was the second highest and you wanted top 3. From what I get, the result would be 25 and 22 but in reality you would want then next highest also (19) - forgetting the 23. How do I do that?
Great Mike
Glad you like it, Salam!!!
Awesome!
: ) : )
Hello Mike, can this be done with windows 10?
Sorry, this is cool and I'm watching on my phone and do not have computer with me.
Always amazing content, Thank you!
It is done with Office 365, Insider, Excel. It is cool, glad you like it DubCmusicTV!
Mike, can you do a video on limitations and drawbacks of Dynamic Arrays?
There are not many drawbacks to Dynamic Arrays, especially compared to the old ways. If you want a formula solution and the solution delivers more than one answer, there are virtually no drawbacks. It is is almost all upside with these new formulas. I have done a comprehensive video here that goes over some of the potential drawbacks, like if you open it in an older version: th-cam.com/video/7jJMDGZpjSk/w-d-xo.html
@@excelisfun I mean things like using them within tables
3:08 conditional formatting
Okay, working on using this formula but when I go to sort it using the -1 it sorts mine in reverse order (smallest number on top)...is there something different we should be typing instead of -1?
I am a proud owner of *The Book* and am working my way through it. Sadly, I've got stuck on sheet Ch12(8-9). When attempting the formulas =LARGE((D10:D17,G10:G17),I11#) or =LARGE((D10:D17,G10:G17),I12#) in cell J12, I get a #REF! error. Any chance of a quick video on this explaining what I'm doing wrong? It might benefit other people too.
Thanks.
#REF! error means the cell reference has been deleted or the reference does not exist. For example, if you have the formula =SUM(A3:C3) and you delete the A Column, you get a #REF! error because the reference A3:C3 was pointing to a range that got deleted. Delete your formula are try again.
@@excelisfun
Thanks for getting back to me so quickly.
I understand why the first formula returns the #REF! error. I11 isn't a number but text "Number" and deleting the formula in I12 makes the formula in J12 refer to an empty cell. Ultimately, I cheated and looked at the answer sheet.
Cell I12 reads =SEQUENCE(J9) where J9 is N (5) of the top N. This changed the error in J12 to a "top 5" spilled array in the range I12:J16. Maybe I got confused or misread the instructions, but I found no instruction to enter a formula =SEQUENCE(cell containing Top N). Besides which, I understood the =SEQUENCE function requires rows, columns, start, step so it would never have occurred to me to use =SEQUENCE to reference a single cell. 🤷♂
@@ianl1052 It sounds like you got it sorted out. Glad you got it sorted out and that you are working hard to study the book : )
I'm using Large to select the top 5 from a set of numbers with duplicates (e.g. 1900,1600,1300,1300,1000,880,880) but, even though 880 is the 5th biggest number, Large only selects the first 5 entities that have 1300 or more. So it isn't behaving like you suggest where using 3 returns the 3rd largest number. Why would it do this?
thanks mike its very interesting but my office 365 doesn't have this formula.
You have to have Insider. In Excel, you can go to File, Account, and sign up for Insider.
what does "hurdle" mean in this viedo? I referred to a dictionary which says "hurdle" means obstacle or or a vertical frame for jumping.
Yes, hurdle is an obstacle for jumping in this example too. 22 is the hurdle and the other numbers can only jump over and be in the delivered set of records if they are bigger than 22. It is another way to use hurdle. It is common in business to say did the number get past the hurdle.
@@excelisfun It's amazing! Why i cant find this meaning in the dictionary. Thank for your explanation!
@@shuboliang2677 It is probably because I am using the word more as a metaphor. If you look hurdle rate, then you see a definition in finance that is close to how I use it here.
@@excelisfun Thank you! i have learnt not only excel but also English after watching your video.
How to find region-wise top 3 data from the database? please help...