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.

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 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.

4 Responses to Rank in subgroup… RANKIF?

  1. 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

Comments, suggestions, corrections are welcome.

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 )

Google photo

You are commenting using your Google 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.