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
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.
@@excelisfun Hello! I've deployed this in a cool poker calculator I have created, but have hit a roadblock with a next level application of this. I have gone to town on it and found some success, but keep slamming into an issue that I'm hoping you could help with. Would you be available to help me with this? Would email be best? Here's a general description: I have found a situation where I need to remove lines of the sorted rows based on a few conditions. I went through many IF/AND/ORs and many combinations thereof. I was successful in removing the rows that meet the conditions, but now the issue is that I cannot figure out how to elevate (if you will) the remaining rows to the top of the sorted list. Or how to re-sort the new list. I hope that's clear enough.
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
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)
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.
I have some basic videos on Access: youtube [dot] com/course?list=ECB0DB785B9C9BE8A8 Great UA-cam 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
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!!
Thanks a lot for your comments on my UA-cam channel. Now I'm looking for a way to show the correct ranks without column C. Still waiting for your next video, Andreas.
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.
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 ! ! !
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...)
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!
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!
Very Good Sir, Its nice I like this. But I find the difficulty if we have same number then the name is repeating. Who we can over come if we are using Ms office 2013. Please help regrading this....
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".
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 ?
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.
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 UA-cam site about Access: youtube [dot] com/user/learnaccessbycrystal
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...
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
Hello - is it possible to have this work on the sum of a range? I.E If you have the same name repeat on another line, how could you have it sum the number of visits by the managers name and then rank?
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 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.
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!
Hi, great video. I posted a message but do not see it, so here it is again, hopefully not twice. Trick 967 is what I require for our baseball sandbag team to keep track of top players, etc, we have men and women and I would like to have a list of top 5 men players and a list of top 5 women players. I've tried adding a if statement but can't get it to work. What needs to be done in the formula to do this?
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??
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
hi, i did it step by step followed all your instructions, but when all the values is changed to zero, then zahid appears to all cells in the manager column. How do we remove that and leave a blank cell when there are no number inputs on the visits column?
Is there a way to put the number of visits in descending order instead of ascending order? In your example it would rank the least number of visits 1st and the most visits last.
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.
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
I am a bit desperate for help. What I need to do is the same as to search a recipe inside other recipes. Assuming that all ingredients are expressed in a number of columns (assuming N columns), and each row contains the record of the recipes with non-zero in the columns corresponding to the ingredients, I need to find out whether the same non-zero ingredients appeared in other recipes, starting from the smallest recipes with the least ingredients (counting ingredients for each row has already been done and sorted from bottom up, smallest in the bottom). How do I go from here? Appreciate some light! Thanks.
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..
Hi I have a row of 30 cells with multiple values. These values may be duplicates. And I want to extract the largest 8 values and to ignore the duplicates. For example, if there were 5 cells with the value 77, I want the 77 to appear only once. I tried many and different way but nothing works. HELP ME PLS
Hiya. This video is awesome as it is the answer to what I desperately need. But, I need to use it in google sheets and cant quite get it to work. Can anyone assist with this? Many thanks in advance.
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
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.
Really well done! Thank you for your clarity! You rock!
You are welcome!!!
@@excelisfun Hello! I've deployed this in a cool poker calculator I have created, but have hit a roadblock with a next level application of this. I have gone to town on it and found some success, but keep slamming into an issue that I'm hoping you could help with. Would you be available to help me with this? Would email be best?
Here's a general description: I have found a situation where I need to remove lines of the sorted rows based on a few conditions. I went through many IF/AND/ORs and many combinations thereof. I was successful in removing the rows that meet the conditions, but now the issue is that I cannot figure out how to elevate (if you will) the remaining rows to the top of the sorted list. Or how to re-sort the new list. I hope that's clear enough.
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
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)
This guy is a great presenter. Clear speech.
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! I am happy it helps!
You are welcome! I am happy to help!
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.
I have some basic videos on Access:
youtube [dot] com/course?list=ECB0DB785B9C9BE8A8
Great UA-cam site about Access:
youtube [dot] com/user/learnaccessbycrystal
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 : )
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
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!!
Cool! I am glad that you liked the video!
I feel he is a great Teacher
very easy to understand
Keep it up
Thanks a lot
give us about more about Access
Thanks a lot for your comments on my UA-cam channel.
Now I'm looking for a way to show the correct ranks without column C. Still waiting for your next video, Andreas.
That trick was Fantastic Mike. Admire all your passion for Excel and to share this knowledge with the Crew !
I am glad that the video helps!
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.
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!!!!
Ah man, this has literally saved my whole spreadsheet model. Thank you so much!
Cool! I am glad that you like this video!
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...)
Welcome Owsome bro just keep it up 👍👍👍👍 fabulous
: ) : ) : ) : )
You are welcome!
Great video! saves me whole day trying to figure out how to extract top 10 numbers with names from a pivot table
i did it ,it works and i dont know how it work but it gives me what i want
It does seem weird, but it is the syntax that the COUNTIF function requires in this situation.
What an amazing teacher you are!
Glad you like it, Robert! Thanks for your support : )
You are welcome for the knowledge!
you are amizing ...this is summery for hundred of your vedios....thanks so much
I do not have a handout or video on the generic rules of how to put functions together. Sorry.
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!
I am trying to consume this very valuable knowledge , and scratching my head too as it's not easily understandable for novice like me
you saved my life! THANKS!
You are welcome!
Great video Sir, I am trying return value matching different columns but row function doesn't help
Wow this video saved my day. thank you!
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 have a video on that particular situation. For back and forth dialog to get customized Excel solutions, try:
mrexcel [dot] com/forum
Wow! Beautiful setup with the RANK! I can't wait to see your video!
thank you so much! this really help me to analyze data much much faster!
Glad this helps, Ammar!!!
Very Good Sir, Its nice I like this. But I find the difficulty if we have same number then the name is repeating. Who we can over come if we are using Ms office 2013. Please help regrading this....
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
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 ?
Amazing video... So helpful.. Thanks a lot Mike
Glad I can help : )
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.
Wow. That is amazing. Thanks Mike for this EXCELlent video.
Is there a way to extract top values for displayed cells only? Amazing lesson btw, thanks!
Thank you for sharing this information! Worked perfectly!!!
This was amazing. Great Job! One of my favorites.
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 UA-cam site about Access:
youtube [dot] com/user/learnaccessbycrystal
I Love you!!!!! I've saved so many time!! You make my day!!
Yes, you can make a re-make of it in German!
Excelisfunner says hi to you!
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...
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
Great comparison Mike. Your voice sounds a little different, better less background noise. Did you get a new mic or something? Thanks.
It is really nice. I could not get excel file. please give me exact link
You are awesome, the video is just exactly how i need it. Thank you so much!!!!
Hello - is it possible to have this work on the sum of a range? I.E If you have the same name repeat on another line, how could you have it sum the number of visits by the managers name and then rank?
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
Your work is excellence
Dear, please tell how can we use small function in the same sheet by leaving zero and counting from above zero
That is too bad that you can't install 1013. But no worries, Excel 2010 or 2007 still are TOTALLY amazing!!!
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.
Thanks!! This series is very helpful. Please keep posting these excellent excel tricks and tutorial.
Regards,,
GK
Thank you for the links
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!
Mate really appreciated, you help me a lot buddy. Thank you very much.
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 :-))))
Hi, great video.
I posted a message but do not see it, so here it is again, hopefully not twice.
Trick 967 is what I require for our baseball sandbag team to keep track of top players, etc, we have men and women and I would like to have a list of top 5 men players and a list of top 5 women players.
I've tried adding a if statement but can't get it to work.
What needs to be done in the formula to do this?
Thank you sir today I learned new things from you
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??
Great wrk 👍👍 thanks for your vedio
You are welcome, s.kishore!!!
Thank you so much for this sir this is really great..
You are welcome, jhiechel1!!!
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
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
hi, i did it step by step followed all your instructions, but when all the values is changed to zero, then zahid appears to all cells in the manager column. How do we remove that and leave a blank cell when there are no number inputs on the visits column?
Is there a way to put the number of visits in descending order instead of ascending order? In your example it would rank the least number of visits 1st and the most visits last.
Thanks a lot. It helped me very much
You are welcome, a lot! Thanks for your support with your comment, thumbs up and Sub : )
Great thanks ! And great formula !
What to do when in top ten more than 1 duplicate value
AWESOME!!! Thank you so much. I have been trying to figure this out for like a year :D
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.
Thanks for the great tutorial (again). I would like to produce a german remake of it.
Greetings from Germany - also to ExcelIsFunner
amazing trick. now i can make pareto charts fast and easy
Hi. If you happen to read this. I am working on a data where I have to extract the lowest 5 numbers ignoring zeros. . How to ignore the zeros?
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
Hi sir in the case of the tie can we add criteria also for the ranking?Thanks
I am a bit desperate for help. What I need to do is the same as to search a recipe inside other recipes. Assuming that all ingredients are expressed in a number of columns (assuming N columns), and each row contains the record of the recipes with non-zero in the columns corresponding to the ingredients, I need to find out whether the same non-zero ingredients appeared in other recipes, starting from the smallest recipes with the least ingredients (counting ingredients for each row has already been done and sorted from bottom up, smallest in the bottom). How do I go from here? Appreciate some light! Thanks.
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..
Is there any substitute for the aggregate formula in Google sheets
Hi
I have a row of 30 cells with multiple values. These values may be duplicates. And I want to extract the largest 8 values and to ignore the duplicates.
For example, if there were 5 cells with the value 77, I want the 77 to appear only once.
I tried many and different way but nothing works.
HELP ME PLS
Really good video but I have a question. What do you do if you have more than 2 duplicates? It comes up as an error every time.
Dates are serial numbers, so just use the number part of this video.
Hiya. This video is awesome as it is the answer to what I desperately need. But, I need to use it in google sheets and cant quite get it to work. Can anyone assist with this? Many thanks in advance.