Ranking items in excel.

While searching for how to rank items I wasn’t happy with the solutions I found, they weren’t intuitive and none worked well for ranking within a group. I think I have a solution that is both readable and works well for many types of rankings.

The countif function typically counts items that are like one another, which makes it great for group ranks, to further extend countif you can use countifs for groups which may involve multiple columns to determine groups, or even use it for more complex rankings as I’ll show.

In the most straightforward case you may have a list of numbers in column A and you need to order them, to do this the function “=countif(A:A,”<“&,A2)+1” would provide how many numbers are less than A1, you could carry the function on and get a list that looks something like this example below:

Excel lacks a rank function, but one can be built using countif. The screenshot shows a simple rank done using “=COUNTIF(A:A,”<=”&A2)”. This will cause some issues if the data contains duplicate values, but works well for unique data sets.

In a more complex situation numbers may countifs allows for rankings to incorporate a group that needs to be ranked. In the example below it ranks based on group in column A and numbers in column B need to be ranked the formula looks like “=COUNTIFS(B:B,”<“&B2,A:A,A2)+1”. This allows for checking that an item belongs to the correct group and then ranking them. So for instance if column A is a region and B is sales, each member of the region is ranked only against it’s own region.

Ranking with countif can have it’s problems if you aren’t carefull. Issues such as duplicate numbers may lead to multiple items of the same rank. This may or may not be desirable, but if it’s not intended there are still workaround. With countifs you can rank over multiple columns, or just rank by order items appear on the sheet with a formula such as “=COUNTIF(A:A,”<“&A2)+COUNTIF($A$1:A1,A2)+1” This method looks for items less than the one in cell A2, then goes on too look for items in the sheet equal to A2 which occurred previously. This prevents duplicate ranks. There are many more options for various use cases, but I think this provides a better solution than many I have seen.

Reducing run time of reports

Many of the reports I run are a mashup of data from multiple sources which may contain millions of data points. Combining these reports often locked my computer up for several minutes making it impossible to do anything while they ran so I needed to look for any way I could cut down the processing… Continue reading Reducing run time of reports

Searching a text string

When asked to pull an eight digit account number out of a random string there are many methods that can be used. I will attempt to explain my method and some of the reasons why I chose it, but first I think I should mention more about the problem. The strings in question were made up… Continue reading Searching a text string

print(“first post”)

Hello, I plan to use this site to explain how I work through some of the projects I have done professionally as well as independently. I hope to explain some of the thought process as well as well as some code or at least psudo code in the case of larger concepts.

Loading…

Something went wrong. Please refresh the page and/or try again.