This is so helpful thank you. Please can I ask how to then apply a sequential date range to the rank by branch outputs? For example, for Atlanta Rank 1, a start and end date, and then for Atlanta rank 2 to start after the end date of Atlanta rank 1? Hope that makes sense!
How do we use the formula in column "Rank by Branch" if the values in column "Score" are in the text format with values like "088, 100, 066, 068, 087, 066, 067, 076, 060 and 062". I understand a simple option is to manually convert these text values to numbers in the column first before applying the formula in the "Rank by Branch" column. But I want to do it through formula which would convert the column array from text to number before doing the comparison. Is there a way to do that? Have an office project where I'm stuck wondering how I can achieve this.
Nice, very good but what i was looking to do was to rank on two columns at the same time, so rank by column 1 first but if there are 2 (or 3) values in column 1 that are the same then we rank in column 1 by the values held in column 2.
Chris, how would you go about doing an overall rank across several different scores. For Example, a team is measured across 3 different metrics, say productivity (higher the better), sales, and a survey score where the higher it is the better. 10 members rank differently across all three metrics, how do you show a combined overall rank?
I am trying to rank 2 seperate columns using the countifs and assumed you just add another section like i did. However Excel is giving me an error, complaining about a problem with the formula. is this NOT how to rank 2 different sets of data to one group of sample space? =COUNTIFS($B$3:$B$16,B3,$F$3:$F$16,H3:H16,">"&F12,H11)
Hi great use of the countifs function! One question ! How do i sum a column descending by groups. I use the sumif function but i get a cumulative sum by groups ascending.
Chris, Thank you! I have a table with Columns (Item, Date, Puchased units,Rate) Item > aaa ,bbb, ccc , aaa , ddd, ccc, aaa, ddd, bbb / Date > 2/01/2020 , 2/01/2020 , 2/01/2020 , 23/01/2020 , 23/01/2020 , 23/01/2020 , 15/02/2020 , 15/02/2020 , 15/02/2020 / Purchased Units > 5 , 3 , 6 , 4 , 5 , 4 , 4 , 6 , 6 / Rate > 50 , 24 , 35 , 48 , 15 , 32 , 51 , 14 ,23. This table keeps track of the purchases I make for a business. Sometimes I source the same item every 20 days or so. I have another table for the items sold, I need that table to update automatically with the exact rate according to the total inventory I have for the item sold. For instance I just sold a unit of item (aaa) and in inventory I have 5 . So the item I sold is the fifth I had in inventory. That unit was purchased on january 23rd 2020 because the last time I purchased the item was on 15/02/2020 just 4 units. I need to extract the right rate. I have seen videos they pull out the last date. But here If I extract the last date for (aaa) i would get the rate = 51 which is wrong since the unit I sold has a a rate = 48. I did SUM.IF(A2:$A$2;A2;$C$2:C2) and I get the cumulative sum of the units for every duplicate > 5 , 3 , 6 , 9 , 5 , 10 , 13 , 11 , 9. I need to get > 13 , 9 , 10 , 8 , 11 , 4 , 4 , 6 , 6. The reason for this is that in row(4) I will get an 8 and If I find the right formula to tell excel to find first the group (aaa) and to match the closest approximate number of cumulative sum for "aaa" = 4 , 8 , 13 . I will get the cell reference for 8 and get the 48 Which is the right answer. If you know a better solution I would appreciate it. Thank you very much for your help.
Chris, I have an excel video for you. It should help out a few people. It is about ranking if you have multiple scores and there is a tie on the highest score. The next highest score is then used along side the first. Let me know if you are interested and I can send it to your email.
Deeply Appreciate your perfect teaching.
Thank you so much. The "Rank by groups" is very helpful for me. What a genius you are!
You're very welcome!
Thank you very much. Especially this “rank by groups” trick - it saved alot of my time!
You're welcome!
used this formula recently to help a colleauge and it saved them hours!! thanks!!
Thanks for letting me know. It is a great function.
Thanks a lot. I spent almost two hours in RUclips to find this trick!
Thanks, Adel.
That's exact what I was looking for, since filter function apparently is not accepted within rank function.
Very ingenious solution. Thanks a lot!!!
You're welcome!
Thank you, Chris. This helped me a great deal to accomplish an urgent need at work today.
Excellent! Thank you!
Thanks a lot for creating this video! It's taught me a lot, especially "pressing F4 to make the range absolute"!
This was just the help I needed today. Thank you!
Glad I could help! Thanks, Diane.
That's a great solution for the ranking in group, thank you!
Thank you Chris! I was looking for a way to enumerate list of categories and this came in handy
You're very welcome!
Thanks Chris, really helpful, exactly what I was looking for!
Helped me on my assignment, thank you Chris!
Happy to help!
Thank you so much! This is exactly what I needed.
You're so welcome!
YOU ARE A GENIUS!!!!
🤯
Thank you! This was clearly explained, and way better than the udemy course I was taking, lol. 😁
Great to hear!
Just what I was looking for. Thanks for the vid! You got a new subscriber.
It's really helpful for me. Thank you very much!
You are welcome!
AMAZING!!!!! YOU'RE A LIFE SAVER!
Thank you.
Group ranking formula is skipping some number in large data set when repeated number obtain.
Thanks for this interesting and helpful video.
I'm glad you liked it. Thank you, Roy.
Great video. Can the result be displayed as "1 of 5"?
just great, i HAVE DUPLICATES AND I WANT TO SORT THE,M IN ASCENDING OR DECENDING, BUT THIS PART WAS REALLY HELPFUL
Thank you.
So very helpful. Thank you so much
You are so welcome!
Thank you! This helped a lot.
You are welcome.
Excellent solution. Thank you!
You bet! Thanks, Craig.
Chris, how would you handle the rank if there are duplicate numbers in the scores?
Here is a workaround I used for a chart. ruclips.net/video/rF7EtT3rfPc/видео.html
How would I list the Branch names in ranked order, thanks.
Great vedio, very well explained 🤟
Thanks a lot 😊
Can you explain why this works?
Thanks Chris, THis was helpful
Thank you very much, its really helpful.
Glad it was helpful!
Chris kaka , Thank you(dhanyawad 🙏🙏) from india
Thanks for this, how do you introduce within your formula a bit to uniquely rank?
This is so helpful thank you. Please can I ask how to then apply a sequential date range to the rank by branch outputs? For example, for Atlanta Rank 1, a start and end date, and then for Atlanta rank 2 to start after the end date of Atlanta rank 1? Hope that makes sense!
Ive 10 diff.numbers in the rows and I want to scoring only from 1 to5 . How do I go about this
How do we use the formula in column "Rank by Branch" if the values in column "Score" are in the text format with values like "088, 100, 066, 068, 087, 066, 067, 076, 060 and 062". I understand a simple option is to manually convert these text values to numbers in the column first before applying the formula in the "Rank by Branch" column. But I want to do it through formula which would convert the column array from text to number before doing the comparison. Is there a way to do that? Have an office project where I'm stuck wondering how I can achieve this.
Nice, very good but what i was looking to do was to rank on two columns at the same time, so rank by column 1 first but if there are 2 (or 3) values in column 1 that are the same then we rank in column 1 by the values held in column 2.
Thanks man! Kam ho gaya
hey I was having problems ranking my data with percentages any tips
How do I rank the top 3 in different colors, that whoever is in rank 1-3 the color change automatically. Please help.
Still do the Rank, but also use Rank with Conditional Formatting.
How can we get the cumulative score basis rank value?
Great work, thank you.
Thanks for watching!
Chris, how would you go about doing an overall rank across several different scores. For Example, a team is measured across 3 different metrics, say productivity (higher the better), sales, and a survey score where the higher it is the better. 10 members rank differently across all three metrics, how do you show a combined overall rank?
It sounds like you want a weighted average. You would add a helper column in Excel and do the weighted average.
THANK YOU THANK YOU THANK YOU!!!!
Glad to help. Thanks, Paul.
Nice vdeo, do we have this GROUP WISE ranking on google sheet??
Its helpful, thankful.
welcome.
Excellent exactly what I wanted
Tony, I appreciate the positive feedback. Thank you.
Thank you so much.
You are welcome.
YOU ARE RHE BEST
Tks Sir, my problem done.
🙏🙏🙏
Welcome 👍
I am trying to rank 2 seperate columns using the countifs and assumed you just add another section like i did. However Excel is giving me an error, complaining about a problem with the formula. is this NOT how to rank 2 different sets of data to one group of sample space? =COUNTIFS($B$3:$B$16,B3,$F$3:$F$16,H3:H16,">"&F12,H11)
Hi great use of the countifs function! One question ! How do i sum a column descending by groups. I use the sumif function but i get a cumulative sum by groups ascending.
Edwin, give me an example of what you are trying to do, please.
Chris, Thank you! I have a table with Columns (Item, Date, Puchased units,Rate) Item > aaa
,bbb, ccc , aaa , ddd, ccc, aaa, ddd, bbb / Date > 2/01/2020 , 2/01/2020 , 2/01/2020 , 23/01/2020 , 23/01/2020 , 23/01/2020 , 15/02/2020 , 15/02/2020 , 15/02/2020 / Purchased Units > 5 , 3 , 6 , 4 , 5 , 4 , 4 , 6 , 6 / Rate > 50 , 24 , 35 , 48 , 15 , 32 , 51 , 14 ,23.
This table keeps track of the purchases I make for a business. Sometimes I source the same item every 20 days or so. I have another table for the items sold, I need that table to update automatically with the exact rate according to the total inventory I have for the item sold. For instance I just sold a unit of item (aaa) and in inventory I have 5 . So the item I sold is the fifth I had in inventory. That unit was purchased on january 23rd 2020 because the last time I purchased the item was on 15/02/2020 just 4 units. I need to extract the right rate. I have seen videos they pull out the last date. But here If I extract the last date for (aaa) i would get the rate = 51 which is wrong since the unit I sold has a a rate = 48. I did SUM.IF(A2:$A$2;A2;$C$2:C2) and I get the cumulative sum of the units for every duplicate > 5 , 3 , 6 , 9 , 5 , 10 , 13 , 11 , 9. I need to get > 13 , 9 , 10 , 8 , 11 , 4 , 4 , 6 , 6. The reason for this is that in row(4) I will get an 8 and If I find the right formula to tell excel to find first the group (aaa) and to match the closest approximate number of cumulative sum for "aaa" = 4 , 8 , 13 . I will get the cell reference for 8 and get the 48 Which is the right answer. If you know a better solution I would appreciate it. Thank you very much for your help.
excelent video!! 5/5
Thank you.
Thank you!
You're welcome!
Thanx very helpful
Chris, I have an excel video for you. It should help out a few people. It is about ranking if you have multiple scores and there is a tie on the highest score. The next highest score is then used along side the first. Let me know if you are interested and I can send it to your email.
Thank u
Welcome
amazing