Forum Discussion
listing items in ranked order
I need a formula to list (in a new column) the values from one column that correspond with rows with the highest values in another column, and then the next highest, then the next. In other words, I am trying to create a ranked list of items from one column based on their "scores" in another column, such that the items that score highest are listed first, then the next highest, and so on. The number of items that fall into each category (highest, next highest, etc.) must be flexible, because the scores change each time the spreadsheet is used. If two items get the same score, it doesn't matter which order they are listed in with relation to each other.
For example, if my data look like this:
A B
1 Cat 6
2 Dog 2
3 Bird 5
4 Horse 6
5 Fish 8
6 Cow 2
7 Sheep 5
8 Duck 4
9 Shark 6
10 Whale 8
Where the animals in "column A" are the items and the values in "column B" are their scores, I want to find a formula that lets me re-list, in a new column, the animals with the highest, then next highest, then next highest scores. So for this specific example the result would look like:
C
1 Fish
2 Whale
3 Cat
4 Horse
5 Shark
6 Bird
7 Sheep
8
9
10
However, the formula needs to be responsive to changes in the score values which might result in more or less animals falling into the highest score category and so on.
I have been playing with the LARGE, INDEX, and MATCH formulas, but I do not understand any of them fully enough to make this work.
Please let me know if you need more info.
Thanks!
willowbe Must admit that not reading a question properly can create problems. I've now created a work-around by introducing a helper column. In this column a unique ranking number, a fraction lower than the real one, is calculated. This will make my original formula work as it was planned to. Have a look at the attached workbook.
9 Replies
Until you become an Insider, I believe the following will do the trick.
=INDEX($A$2:$A$10,(MATCH(LARGE($B$2:$B$10,ROW()-ROW($B$1)),$B$2:$B$10,0)))Read more about the Insider program here:
Riny_van_Eekelen Hmmm, this seems to get me part of the way there, but it returns just the highest then the next highest, and so on, rather than all of the items with the highest score, followed by all of the items with the next highest, etc. Also, it returns each item three times in a row.... In other words, instead of:
1 Fish
2 Whale
3 Cat
4 Horse
5 Shark
6 Bird
7 Sheep
8
9
10
I am getting:
1 Fish
2 Fish
3 Fish
4 Cat
5 Cat
6 Cat
7 Bird
8 Bird
9 Bird
10 Duck
etc.
Have I done something wrong?
Thanks so much!
willowbe Must admit that not reading a question properly can create problems. I've now created a work-around by introducing a helper column. In this column a unique ranking number, a fraction lower than the real one, is calculated. This will make my original formula work as it was planned to. Have a look at the attached workbook.
cdolan27 I love the simplicity of this suggestion but "SORTBY" does not appear to be a working function in my version of Excel! I'm using Excel as part of Office 365, which I thought meant I would get all the newest bells and whistles, but a quick Googling tells me that SORTBY is in beta, and has only been rolled out to a "select number of office insiders." nikita how do I become one of these insiders???
