Every time i need a tutorial from Excel, i check Leila's videos before i go to another channel, because she explains it so well. Not like other guys with one hella heavy accents who just uses random formula or whatever without explaining the logic then be like "Ta-da there you have it". Tbh im so grateful for Leila's channel.
Hello Leila, Thank you for showing the Textjoin function when listing employees by rank - brillant! As for ranking without duplicates, what I typically use (because I can work my head around it) is the following formula which would be in cell D5: =RANK.EQ(B5,$B$5:$B$24)+COUNTIF(B$4:B4,B5). Alternatively if the column headings were only numbers (or the data started in the first row) it would change to =RANK.EQ(B5,$B$5:$B$24)+COUNTIF(B$5:B5,B5)-1. Love your tutorials! Thank you! Michael
Thank you Michael - Agree, the unique rank formula is great to avoid duplicate numbers. I use that when creating bar charts that are automatically sorted. Thank you for sharing.
I've problem Roll No Name Total Mark GPA Grade Positon 202518 Gabriel 522 5.00 A+ 1 202536 Michael 519 5.00 A+ 2 202542 Israfil 529 4.83 A 3 202585 Izrail 528 4.83 A 4 202530 Munkar 516 4.58 A 5 202533 Nakir 490 4.58 A 6 202532 Raqib 472 4.33 A 7 202548 Atid 472 4.33 A 7 202552 Malik 457 4.25 A 9 202555 Ridwan 435 4.17 A 10 Used Function: =RANK.EQ(D2, $D$2:$D$11) + COUNTIFS($D$2:D2, D2, $C$2:C2, ">" & C2) / =1 + SUMPRODUCT(($D$2:$D$12>D2) + (($D$2:$D$12=D2)*($C$2:$C$12>C2))) Don't skip Number. 9 to be 8. How I Solve it?
I had to give it a moment before i fully understood what is going on. So you're valuating entries bigger then currently checked but with proportional contribution. So clever, i love it.
Respected Madam, For ranking purpose i watch dozen of videos in past, so i watch your video today and i find my problem of ranking without skipping number, so you are a great and i am thankfull to you for making this video, be sure this video solve my problem which i facing from 6 years, thank you so much
Hi Leila.. great video! Thanks for the SUMPRODUCT, COUNTIF and TEXTJOIN fun!! Before enrolling in your classes and watching your channel, I would have been clueless as to what you did here. Thanks to your great instructions and clear explanations, I can now easily follow along and understand your approach, as well as replicate your solutions and extend them to other uses. For anyone wanting a deep dive into these great techniques, I strongly recommend that they seek out all your offered resources.. both free and paid. You won't be disappointed and you will up your game in EXCEL. In recreating your example, I realized that you might want to sort the results for the ranks with multiple answers. With DAFs, that would be easy.. wrap the TEXTJOIN function with SORT().. yes? Can't wait until those are released by MS!! Thanks again for all that you offer to the EXCEL community. Your contributions and resources are invaluable. Thumbs up!!
Dear Mam, I have no words to thank you. Super trick. I was in trouble for the last two years while developing the result sheets of my school and I was unable to find a way... You are genius. May God bless you.
For people who do not have Textjoin, an alternative would be to make a sequential list of 1-20 beside column D unique rank. Then in the report tab in cell D6 enter =INDEX(Data!$A$5:$A$24,MATCH(Report!C6,Data!$E$5:$E$24,0)
That's a good way of ranking, especially the use of sumproduct and countif especially when ranking the top 3, this method will not leave out individuals with the same rank
Thanks Solomon. Yes - using the usual Vlookup would get the first match - Textjoin is great for this. In the future with dynamic arrays, the formula and the steps will also get more simple :)
I watched this video a while a go and it blew my mind. Today I had to create a ranking and realised I didn't want to skip positions, so came back to watch it again. Thank you!! My question is if I want to add another criteria in order to avoid repeating positions (and not skip positions as well). I can't join both tricks unfortunately.
Hello Leila Thank you for the videos... you're showing how to get the results with explanation. in regard to ranking, I used to go with the largest, smallest functions, and I use < or > for ranges with countif
@@LeilaGharani Hello Leila Gharani, I just wanted to share, this is the best I came up with in excel and VBA few years ago, and shared it on youtube, almost, 6 months ago th-cam.com/video/pZZBhH2un5Q/w-d-xo.html it uses VBA, conditional formatting and in sheet excel function it has some features I was looking for and Thankfully were available in Excel 2007 and I wanted to share my music as well :) and again thank you for your excellent Videos
And This one as well, th-cam.com/video/Qk3v991FcZo/w-d-xo.html When I first started excel, back then you were a reference, before VBA. it's all conditional formatting and a few if statements Thanks
I recently found your channel. Your explanations are very clear, your example are well thought of and relevant for many applications. Keep up the EXCELLENT work of spreading knowledge !!
everything in your channel is very interesting. I'm using this tool as part of my work and requirements as a teacher, but i never had any idea of the other functions. Thank you for sharing your knowledge online.
Thanks Leila love your work, helps so much when Excel is not your daily work routine . Excel 2019 - Tried this video tutorial to get info into your earlier tutorial "Excel Charts: Sorted Bar Chart as Alternative to the Pie Chart 14.7.2016. As I have table of data that now happens to have 2-3 sets of duplicates or Triples it varies, However the Rankings and graphs are all in one file linking to the data sets in another file so ranking etc are carried out on the separate sheet. Now the issue has come to light that this method is not working on duplicates. So I thought that the ranking should perhaps have been on the underlying data set worksheet but tried that no different the index and match return's an error so tried on the data sheet the other formula and still get an error - these are the two formula. =RANK.EQ(J55,J$55:$J$219,0)+COUNTIF($J$55:J55,J55)-1 or =SUMPRODUCT((J55
I call this the Leila Ranking formula. I used it in an online Teams session (on someone else’s computer) last year and the people were amazed. Conclusion: it’s always good to have a Leila Gharani formula on hand - you never know when you might need it. ;-)
Great solution with the sumproduct, but if some of your data are equal and already listed one after the other (In your video for example all the 9.000s are together) the formula breaks. For example the sumproduct resulted to something that looked like an whole number (i.e integer) but it was not. For example the sumproduct was 8.000000 and no matter how many digits I included it looked like a whole 8 but the INT part of it was 7 !!!!!!! Couldn't get my head around why,....... When I was deleting some of the "same sorted records" (some of your 9.0000s) the formula was working perfectly .....To overcome this I had to totally unsort my data in order to apply your beautiful solution. Thank you very much for your excellent video.
Very very thankful for this.i am teacher in intercollage.i face problem every time while declare position in class. When excel skip the number I do it manually.thanks again.
Thank you for sharing this awesome lesson. I personally gained so much knowledge in excel from you. This functions works perfectly on lists of numericals.But i am having trouble, I applied the SUMPRODUCT+COUNTIF function on a student's Mark sheet, and the formula sees letters as 1 as below: 500 2 250 4 Fail 1 300 3 I know you'd be so busy and I would not complain even if you don't have time for my problem. If it's not bothersome for you, please help me find a solution for this. Thank you for creating an amazing channel.
Thanks so much for all your presentations. I tried with windows 10 the textjoin formula but it's not identifying the names and error message as #Name? Can you help?
Ms Excel, I'm trying to rank numbers in a cells that are actually formulas for a countdown. Background: I'm a uni student and I wanted to see how many days before my final exams inorder to prioritize my studying. The column with this formula works great and I have the countdown per course; however, I am trying to rank my courses from 1 - 7 (I'm taking extra courses - love learning!) and I keep getting an error or a "circle reference" error. How would you rank 7 rows with a formula? I could manually just do this but I was hoping to automate it with a formula. Thoughts?
Hi there! I would like to ask you, if you can use some chart or graph to picture the winner´s tier or just have the first country or the winner at the highest point of the graph while the 1 would be a lowest value and the 150´s coutry lowest rank, but highest value? Is that possible? Thanks
Came here in hopes of finding an explanation on how to (manually determine the) rank for q-grams (my study-script seems to be too advanced for me...) - stayed because it was impressive and very intruiging.
Awesome video thank you very much for your great videos. I have learned a lot. One thing though that I am struggling with is converting this sumproduct formula to a VBA Function. Would you have any pointers or would you make a video on how to convert this formula into a VBA function?
Great Video and a life saver since I had a to report the top 10 vendors from a report couple of days ago. Thanks. I was trying to do it with dynamic arrays and I was not able to do it ( I have the insiders edition) . I was trying to use the # to replace the ranges in D3:D19 (on the rank) like D3# and A3: A19 (vendor) A3#. This is the formula I was trying to used. =TEXTJOIN(", ",TRUE,IF(ROWS(G3:G$3)=$D$3:$D$19,$A$3:$A$19,""))
I'm away this week and don't have my dynamic array Excel version with me to check. But check out the comment from Bill Szysz - he's shared a dynamic array version with INDEX index in TextJoin.
What if you wanted to ranked those people base on group that is the top person in each group. And also return the name of the top person in each group. How would you go about doing that? I predict your insights would be helpful.
Grab the file I used in the video from here 👉 pages.xelplus.com/rank-values-file
A 4 year old vid saves the day!!!! Thanks Leila. You remain an absolute LEGEND FOREVER!
Every time i need a tutorial from Excel, i check Leila's videos before i go to another channel, because she explains it so well. Not like other guys with one hella heavy accents who just uses random formula or whatever without explaining the logic then be like "Ta-da there you have it". Tbh im so grateful for Leila's channel.
Thanks for the kind feedback Daniel!
Hello Leila,
Thank you for showing the Textjoin function when listing employees by rank - brillant!
As for ranking without duplicates, what I typically use (because I can work my head around it) is the following formula which would be in cell D5: =RANK.EQ(B5,$B$5:$B$24)+COUNTIF(B$4:B4,B5). Alternatively if the column headings were only numbers (or the data started in the first row) it would change to =RANK.EQ(B5,$B$5:$B$24)+COUNTIF(B$5:B5,B5)-1.
Love your tutorials! Thank you!
Michael
Thank you Michael - Agree, the unique rank formula is great to avoid duplicate numbers. I use that when creating bar charts that are automatically sorted. Thank you for sharing.
This is brilliant .. thanks Michael.. any tips on ascending rank?
I've problem
Roll No Name Total Mark GPA Grade Positon
202518 Gabriel 522 5.00 A+ 1
202536 Michael 519 5.00 A+ 2
202542 Israfil 529 4.83 A 3
202585 Izrail 528 4.83 A 4
202530 Munkar 516 4.58 A 5
202533 Nakir 490 4.58 A 6
202532 Raqib 472 4.33 A 7
202548 Atid 472 4.33 A 7
202552 Malik 457 4.25 A 9
202555 Ridwan 435 4.17 A 10
Used Function:
=RANK.EQ(D2, $D$2:$D$11) + COUNTIFS($D$2:D2, D2, $C$2:C2, ">" & C2)
/
=1 + SUMPRODUCT(($D$2:$D$12>D2) + (($D$2:$D$12=D2)*($C$2:$C$12>C2)))
Don't skip Number. 9 to be 8. How I Solve it?
I had to give it a moment before i fully understood what is going on. So you're valuating entries bigger then currently checked but with proportional contribution.
So clever, i love it.
Yes :) It does take some time to digest.....
Respected Madam, For ranking purpose i watch dozen of videos in past, so i watch your video today and i find my problem of ranking without skipping number, so you are a great and i am thankfull to you for making this video, be sure this video solve my problem which i facing from 6 years, thank you so much
THE AMOUNT OF TIMES YOU SAVED MY DAY. YOU'RE THE BEST!
That is pretty advanced. Seriously who would've thought something like that could've been done, I love it!! Thank you Excel Queen.
You're welcome Mahmoud :) Soon we'll have it easier with dynamic arrays...
Very neat, clean-cut and handy formulas elaborated in a crisp-clear lecture. Perfect as usual, Leila!
Thanks for the kind words. I'm glad you like the video.
Hi Leila.. great video! Thanks for the SUMPRODUCT, COUNTIF and TEXTJOIN fun!! Before enrolling in your classes and watching your channel, I would have been clueless as to what you did here. Thanks to your great instructions and clear explanations, I can now easily follow along and understand your approach, as well as replicate your solutions and extend them to other uses. For anyone wanting a deep dive into these great techniques, I strongly recommend that they seek out all your offered resources.. both free and paid. You won't be disappointed and you will up your game in EXCEL. In recreating your example, I realized that you might want to sort the results for the ranks with multiple answers. With DAFs, that would be easy.. wrap the TEXTJOIN function with SORT().. yes? Can't wait until those are released by MS!! Thanks again for all that you offer to the EXCEL community. Your contributions and resources are invaluable. Thumbs up!!
Thank you Wayne for your kind words and the thumbs up. Dynamic arrays would make this so much easier. I'll have to do a version on that one too :)
It took me a whole year to find out this solution. Thanks a lot
excellent way to do the rank without skipping numbers.. I was searching for this exact formula.. Thanks a lot..
Thank you so much for all of your videos, especially this one. I'm using this set of functions most days!
Why you don´t have ten million followers is beyond me. Love your work, kick ass as usual. Keep it up. :)
That's very kind Mattias :)
Thank you Leila! This approach to the problem was simply mind blowing
brilliant, Leila, you have just saved my life - again... :D
Dear Mam,
I have no words to thank you. Super trick.
I was in trouble for the last two years while developing the result sheets of my school and I was unable to find a way... You are genius. May God bless you.
You're are very welcome. I'm glad I could help with that.
@@LeilaGharaniWhy doesn't Microsoft offer this as a built-in function choice?
For people who do not have Textjoin, an alternative would be to make a sequential list of 1-20 beside column D unique rank. Then in the report tab in cell D6 enter =INDEX(Data!$A$5:$A$24,MATCH(Report!C6,Data!$E$5:$E$24,0)
Amazing Leila you always seems to go a few steps deeper into the matter, thank you for this one.
Wow, I'd never come up with a solution like that. Amazing! Thank you so much
You're very welcome Affonso. Thank you for dropping by :)
That's a good way of ranking, especially the use of sumproduct and countif especially when ranking the top 3, this method will not leave out individuals with the same rank
Thanks Solomon. Yes - using the usual Vlookup would get the first match - Textjoin is great for this. In the future with dynamic arrays, the formula and the steps will also get more simple :)
Your videos are awesome! I rely so much on your expertise to survive in my job! Thanks loads :)
I watched this video a while a go and it blew my mind. Today I had to create a ranking and realised I didn't want to skip positions, so came back to watch it again. Thank you!!
My question is if I want to add another criteria in order to avoid repeating positions (and not skip positions as well). I can't join both tricks unfortunately.
I'm also searching for this, did u find any solution to this?
exactly what i needed within 2 minutes thanks very much
That is slick and ending part with visualization is astonishing. thanks for sharing
Thanks Haider. I'm glad you like that part.
Thanks a ton.. I was looking for this for a long. U helped me a lot
Exactly what I'm looking for. Thank you my Queen
You are so welcome.
I'm gonna have to get my hands dirty and do some testing to fully understand your ranking function. I love it. Thank you!
Getting hands dirty is the best way to learn :) Thanks Luciano.
Make a sample worksheet and test out the ideas.
Easy and Nice Amazing Tips, before I used =LARGE() formula but duplicate was my nightmare, Leila, thanks for sharing this video!
Thank you so much.......... i spent many hours just to find this
You are brilliant! Clear explanation.
Thank you very Much for your coaching and guidance. It is real and It works
I'm glad the tutorials are helpful Olivier.
Understood the power and potential of using TEXTJOIN function better through this video. And SUMPRODUCT as always never fails to amaze!
TextJoin is one of those hidden super functions :) Thanks for dropping by Sandip.
Fantastic! Was working around without knowing this amazing trick. I always learn something new from your channel.
So helpful. Thank you!
Thank you. If i get promoted in my job would be thanks to ur videos. Again Thank you.
Wish you luck!
Your video is very supporting and u are genius.
Hello Leila
Thank you for the videos... you're showing how to get the results with explanation.
in regard to ranking, I used to go with the largest, smallest functions, and I use < or > for ranges with countif
Thank you Mohamed. Agree - large and small are great functions for ranking too. Thank you for sharing.
@@LeilaGharani Hello Leila Gharani, I just wanted to share, this is the best I came up with in excel and VBA few years ago, and shared it on youtube, almost, 6 months ago
th-cam.com/video/pZZBhH2un5Q/w-d-xo.html
it uses VBA, conditional formatting and in sheet excel function
it has some features I was looking for and Thankfully were available in Excel 2007
and I wanted to share my music as well :)
and again thank you for your excellent Videos
And This one as well, th-cam.com/video/Qk3v991FcZo/w-d-xo.html
When I first started excel, back then you were a reference, before VBA.
it's all conditional formatting and a few if statements
Thanks
Awesome work.i really impressed by your work.
Brilliant...you are so clear in your explanations......but I am still a bit dizzy after that !!…..Very Good Tutorials
Glad you like it Victor. I hope the dizziness was not too bad :)
thank you so much Ms. leila. Thank you so much i have been searching that.
You're welcome 😊
I recently found your channel. Your explanations are very clear, your example are well thought of and relevant for many applications. Keep up the EXCELLENT work of spreading knowledge !!
Thank you for the kind feedback Dane!
Productive session,Thank you
This was exactly what I needed. Thx
You solve me those problems which CHT Gpt couldn't....😊
Happy to help :)
Excellent!!! It worked for me!!! Thank you!!!
Great!
beautiful mind .. superior teacher . big "thanks" , Leila .
everything in your channel is very interesting. I'm using this tool as part of my work and requirements as a teacher, but i never had any idea of the other functions. Thank you for sharing your knowledge online.
Match( Analytical skills, Creativity) = brilliant solution
You are simply great and just brainstorming thanks again!
Thanks Leila love your work, helps so much when Excel is not your daily work routine .
Excel 2019 - Tried this video tutorial to get info into your earlier tutorial "Excel Charts: Sorted Bar Chart as Alternative to the Pie Chart 14.7.2016. As I have table of data that now happens to have 2-3 sets of duplicates or Triples it varies, However the Rankings and graphs are all in one file linking to the data sets in another file so ranking etc are carried out on the separate sheet. Now the issue has come to light that this method is not working on duplicates.
So I thought that the ranking should perhaps have been on the underlying data set worksheet but tried that no different the index and match return's an error so tried on the data sheet the other formula and still get an error - these are the two formula. =RANK.EQ(J55,J$55:$J$219,0)+COUNTIF($J$55:J55,J55)-1 or =SUMPRODUCT((J55
Thank you this is super helpful😮
your videos are the best
A very helpful video! Thank you for this one.
I have so many uses for this! Thank you for this video!
I'm glad you can put this to use :)
great work Leila
Thanks for the video, as always you are awesome with excel. Just wanted to know is there any other alternative for Textjoin function in excel 2016?
This is a great formula!
Mind-blowing logic !
Hi Leila. it was good what u explained.
Beautiful formula, thank you so much👍
Excellent tutorial. Thanks
Glad you like it.
Again another awesome presentation thanks!!!
Thank you Nissim. Glad you like it :)
You just put a big smile to my face :))
I'm glad to hear that :)
I have been looking for this video since 2017. Thanks to you. I'll definitely subscribe your channel.
Glad I could help with that. Great to have you here.
Thanks I did not realize how RANK.EQ related to RANK. Will use the former going forward.
very informative.. Thanks
shandar great keep it up
love u for this leila , i really dint find a better explantion than this
very useful video....very .thanku mam
Precise and concise, well explained, Leila
Very valuable video
I call this the Leila Ranking formula. I used it in an online Teams session (on someone else’s computer) last year and the people were amazed.
Conclusion: it’s always good to have a Leila Gharani formula on hand - you never know when you might need it. ;-)
Glad it came in handy 😁
Great solution with the sumproduct, but if some of your data are equal and already listed one after the other (In your video for example all the 9.000s are together) the formula breaks. For example the sumproduct resulted to something that looked like an whole number (i.e integer) but it was not. For example the sumproduct was 8.000000 and no matter how many digits I included it looked like a whole 8 but the INT part of it was 7 !!!!!!! Couldn't get my head around why,....... When I was deleting some of the "same sorted records" (some of your 9.0000s) the formula was working perfectly .....To overcome this I had to totally unsort my data in order to apply your beautiful solution. Thank you very much for your excellent video.
Great examples...thanks for the video!
Very very thankful for this.i am teacher in intercollage.i face problem every time while declare position in class. When excel skip the number I do it manually.thanks again.
Thank you for sharing this awesome lesson. I personally gained so much knowledge in excel from you. This functions works perfectly on lists of numericals.But i am having trouble, I applied the SUMPRODUCT+COUNTIF function on a student's Mark sheet, and the formula sees letters as 1 as below:
500 2
250 4
Fail 1
300 3
I know you'd be so busy and I would not complain even if you don't have time for my problem. If it's not bothersome for you, please help me find a solution for this. Thank you for creating an amazing channel.
Hey this video is very useful for me... quick question... how to avg a new joiner along with the existing staffs
That is so awesome and I will be putting it to use immediately. Thank you Leila.
Thanks so much for all your presentations.
I tried with windows 10 the textjoin formula but it's not identifying the names and error message as #Name?
Can you help?
Ms Excel,
I'm trying to rank numbers in a cells that are actually formulas for a countdown. Background: I'm a uni student and I wanted to see how many days before my final exams inorder to prioritize my studying. The column with this formula works great and I have the countdown per course; however, I am trying to rank my courses from 1 - 7 (I'm taking extra courses - love learning!) and I keep getting an error or a "circle reference" error. How would you rank 7 rows with a formula? I could manually just do this but I was hoping to automate it with a formula.
Thoughts?
You are brilliant...
Hi there! I would like to ask you, if you can use some chart or graph to picture the winner´s tier or just have the first country or the winner at the highest point of the graph while the 1 would be a lowest value and the 150´s coutry lowest rank, but highest value? Is that possible? Thanks
Came here in hopes of finding an explanation on how to (manually determine the) rank for q-grams (my study-script seems to be too advanced for me...) - stayed because it was impressive and very intruiging.
this is exactly what I want.. thanks a million
My pleasure. Glad you like it.
Amazing!! Keep up the good Work Leila :)
You have an excellent brain 🧠
Very useful - and impressive! Thank you!
This is awesome - but what if you need smallest to largest, with duplicates?
Is there any idea in 2016 version that related to textjoin function?
BEAUTIFUL! Great solution!
more than Great ♥
Awesome video thank you very much for your great videos. I have learned a lot. One thing though that I am struggling with is converting this sumproduct formula to a VBA Function. Would you have any pointers or would you make a video on how to convert this formula into a VBA function?
Hi ma'am, could you please provide a substitute syntex of "Text Join" in excel 2016.
Great Video and a life saver since I had a to report the top 10 vendors from a report couple of days ago. Thanks. I was trying to do it with dynamic arrays and I was not able to do it ( I have the insiders edition) . I was trying to use the # to replace the ranges in D3:D19 (on the rank) like D3# and A3: A19 (vendor) A3#. This is the formula I was trying to used. =TEXTJOIN(", ",TRUE,IF(ROWS(G3:G$3)=$D$3:$D$19,$A$3:$A$19,""))
I'm away this week and don't have my dynamic array Excel version with me to check. But check out the comment from Bill Szysz - he's shared a dynamic array version with INDEX index in TextJoin.
Thanks for your reply. I am going to search for that. I am hooked up with these arrays formulas and the spills arrays.
can you explain why are we dividing with the countif function (frequency of the no.)
What if you wanted to ranked those people base on group that is the top person in each group. And also return the name of the top person in each group. How would you go about doing that? I predict your insights would be helpful.
Wow... Great 👍👍👍👍💕💕💕 💐💐💐💐
Thank you for this video but does this also works in a table ? I get "spill" instead of a ranking.
Mind-blowing ma'am