Rank in subgroup… RANKIF?

Excel tip - Rankif.PNG

To get the overall ranking is easy with the RANK function.  The syntax of RANK

=RANK(Number,Ref,[order])
  • Number is the number we want to rank;
  • Ref is the list of numbers of comparison (could be an array, a reference or a list of numbers);
  • Order, is the optional argument, when omitted or 0 it meanings descending order (i.e. larger number has higher rank); 1 = ascending order (i.e. smaller number has higher rank)

So to get the overall ranking, the following formula does the job effectively:

=RANK(C2,$C$2:$C$7)

How about to get the rank in class?  You may want to look for a function like RANKIF.  Unfortunately, there is no such straightforward function in Excel so far…

So is there a way to solve the problem above?

Array formula is on top of your mind?  If that’s the case, I believe you have quite good knowledge in Excel.  In my observation, majority of Excel users is not familiar with array formula.

Therefore, instead of showing a “complicating” array formula, I will show you how to use COUNTIFS to solve the problem.  Yes, you heard that – COUNTIFS.

Before we start, let’s think about what do we do when we try to rank a number from a list of numbers…

Suppose we have the following list of numbers:

Excel tip - Rankif1.PNG

We can tell very quick that 97 ranks first; 92 ranks ranks third (because there is a tie of 97, resulting in two firsts);  90 ranks forth; and so on and so forth.

But now, a simple question for you:  Why 97 ranks the first?

ummmmm……

errrrrrrrrrr……

well……

because……

See remarks in the screenshot below:Excel tip - Rankif4

To make a generic statement for it:

The rank of a value in a list is equal to the number of values that are higher than the value itself plus 1

In the example, 97 earns the 1st place because there is no value higher than 97 itself. Make sense?

In Excel’s words, it is

=COUNTIF(Range, Criteria)+1

Substitute this into our example:

=COUNTIF($C$2:$C$7,">"&C2)+1  'note the use of & to construct the criteria

The following screenshot shows you the two constructions of the formula using RANK, and COUNTIF as described.  They yield the same result.Excel tip - Rankif2.PNG

Wait… how about with additional criteria? i.e. Rank in class in the example?

Using RANK may not give you an easy solution.  Nevertheless, we may twist the COUNTIF just a little bit to solve the problem.  We will use COUNTIFS, which is very similar to COUNTIF but accepting multiple criteria.

=COUNTIFS(Range1,Criteria1,Range2,Criteria2...)

Substituting the range and criteria in our example,

=COUNTIFS($A$2:$A$7,A2,$C$2:$C$7,">"&C2)+1
  • The first pair of Range & Criteria specifies the condition, i.e. Class;
  • The second pair of Range & Criteria specifies what to look at, i.e. Score
  • The +1 is critical to give you the correct rank.

 

Using the cell F3 for illustration.  The above formula means:

Count scores that are larger than “70” in Class “A”, then add 1 to it.

The following screenshot summarizes the result using different functions.Excel tip - Rankif3

With COUNTIFS, we can do the ranking with multiple criteria easily. 🙂

Try to do the exercise by modifying the formula to do the ranking in ascending order.

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , . Bookmark the permalink.

9 Responses to Rank in subgroup… RANKIF?

  1. 34dfsfdg3 says:

    Hi, suppose in your example there are 2 who scored 97. How do we rank it that one is 1st while the other is 2nd even if both are 97?

    Like

  2. Mukesh Killa says:

    Need to inset a ranking with below criterias. I have this data:

    Name Accuracy
    A 100%
    B 99.60%
    C 99.80%
    And below criteria
    Score 1, if accuracy is <99.70 %
    Score 2. if accuracy is 99.70 % to 99.90 %
    Score 3. If accuracy is 100.00 %

    Please help

    Like

  3. Geoffrey Trawick says:

    Is it possible to use and modify these formulas in a filtered context. By that I mean is it possible to incorporate these rankings in multiple classes with SUBTOTAL for visible only cells (filters on)? – thanks

    Like

    • MF says:

      Hi Geoffrey,
      Try adding a helper column with this formula:
      =SUBTOTAL(103,C2)*C2
      ‘where C2 is the original score
      Then apply the formula using COUNTIFS described in this post to the range of the helper column instead.
      Hope it helps.
      Cheers 🙂

      Like

  4. cyrilbrd says:

    Hi, how to get 1, 3, 2 instead of 1, 3, 1 hence how to NOT skip a rank like is shown in your example where both Angela and Carman got 1st due to both having 97.

    Like

Leave a Reply to Geoffrey Trawick Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.