Formula Forensics 043: Rankifs or Conditional Rank

Excel has had a native Rank() function since its very first versions. This function has been updated in 2010 to include Rank.eq and Rank.Avg.

These functions allow you to Rank a list in either an Ascending or Descending order

Recently on Linkedin I came across a formula at Excel Champs for calculating a Conditional Rank effectively a Rankif() function. The Excel Champs post is based on Michael Girvin’s Youtube Video.

Despite having used Excel since its introduction and despite the fact that there are at kleast a dozen posts in the Chandoo.org Forums discussing Rank If, I thought it strange that I had never seen or had need to use a Rankif() function and so was drawn to it to understand how it worked.

This post will look at how this technique works, how to use it for Ascending and Descending Ranks and then how to extend it to Multiple Criteria.

Then finally we will move the function into the 21st Century and replace the base function that the technique is based on with a newer function.

As always with Formula Forensics posts you can follow along with a sample file, Download Here.

Conditional Rank or Rankifs

What is a Conditional Rank or Rankif/s() function.

Just as the words describe, Conditional Rank is a Rank that is based on conditions. So just as Countif() or Sumif() count or sum based on a condition so does Conditional Rank, effectively it is the missing Rankifs() function.

Open the sample file and look at the data set

You can see that we have the Scores for 12 Students. The table also has fields for Department and Area.

I have highlighted the 4 Engineering Students which we will examine during the post

John has a Score of 38, Chandoo has a score of 72, Donna a score of 62 and Bob a score of 84.

So manually Ranking these 4 students from Highest to Lowest would have the following order

1. Bob 84
2. Chandoo 72
3. Donna 62
4. John 38

This is shown in the Dep’t Wise Rank Asc, column E

So examining the highest Engineering student, Bob, Cell E8 you will see that it has a formula:

=SUMPRODUCT(($B$2:$B$13=B8)*(D8<$D$2:$D$13))+1

lets look at how this formula works

=SUMPRODUCT(($B$2:$B$13=B8)*(D8<$D$2:$D$13))+1

We will refer to these sections later, but the first or Green component is a Conditional part of the formula

The 2nd or Red section is the Ranking part of the Formula.

We know from other Formula Forensics posts that Sumproduct adds “Sums” the Products of its constituent arrays. You can read more about how Sumproduct works here Formula Forensics 007 Sumproduct

In this case there will only be a Single array which is actually made up, as the product of 2 other arrays

($B$2:$B$13=B8)*(D8<$D$2:$D$13)

The Conditonal Section

Lets look at the first array, the Conditional Section

($B$2:$B$13=B8)

This says is range B2:B13 equal to B8

ie: It is saying are you an Engineering Student ?

If you select Cell E8, then select the ($B$2:$B$13=B8) component and press F9

Excel will show: {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

This is an array showing which of Cells in B2:B13 are equal to B8

We can see that the 1st, 4th, 5th and 7th elements of the array are True

{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

These correspond to cells B2, B5, B6 and B8 which are all Engineering.

The Ranking Section

Escape out of that formula and we will now look at the second Array

If you select Cell E8, then Edit the cell F2 and select the (D8<$D$2:$D$13) component and press F9

Excel will show: {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}

This is an array showing which of Cells in D2:D13 are greater than D8

That is the 3rd, 10th and 11th elements are greater than D8

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}

But lets notice here that none of the the 3rd, 10th or 11th elements are Engineering

They are Admin, Sales and Admin, respectively.

So for our cell D8, None of the Engineering Scores are greater than it.

Combining the Two Sections

Escape back out of that formula and we will now look at the internal multiplication of the two arrays

If you select Cell E8, then Edit the cell F2 and select the whole ($B$2:$B$13=B8)*(D8<$D$2:$D$13) component and press F9

Excel will display: {0;0;0;0;0;0;0;0;0;0;0;0}

This array is the product of the previous two arrays

ie:

{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} * {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}

We can see here that none of the True values line up

So that when the arrays are multiplied the resultant array is {0;0;0;0;0;0;0;0;0;0;0;0}

It will only have a 1 in the final array when the two corresponding array elements both have true values.

Finally this array is available to the Sumproduct Function for evaluation

Escape back out of that formula and we will now look at how Sumproduct treats the two arrays

If you select Cell E8, then Edit the cell F2 and select the whole =Sumproduct($B$2:$B$13=B8)*(D8<$D$2:$D$13)) component and press F9

Excel will display a {0}

That is the sum of the products of the arrays is 0

Finally the formula adds a 1 to this to get the final Rank of 1

That is that none of the Engineering Students have a higher score than Bob and so he has a Value of 1

ie: The steps in the solution being

=SUMPRODUCT(($B$2:$B$13=B8)*(D8<$D$2:$D$13))+1

=SUMPRODUCT( {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} * {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE} )+1

=SUMPRODUCT( {0;0;0;0;0;0;0;0;0;0;0;0} )+1

=0 + 1

=1

Lets now examine how Chandoo, in Row 5, went with a score of 72

Select Cell, E5

You can edit the cell by pressing F2

=SUMPRODUCT(($B$2:$B$13=B5)*(D5<$D$2:$D$13))+1

Evaluate the component sections

=SUMPRODUCT( {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} *  {FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE} )+1

=SUMPRODUCT( {0;0;0;0;0;0;1;0;0;0;0;0} )+1

=1 + 1

=2

We can see using the same analysis technique for Row 5, that Chandoo’s score of 72 was the 5th, highest score overall, It had 4 higher scores than Chandoo did.

But only 1 of these was an Engineering student

The 7th element in each array is True

So overall Chandoo had 1 Engineering Student with a Higher Score and so he is gets a Rank of 2.

You can use the technique above to examine other students and see how they Ranked.

Rank Descending

The technique and formulas above use =SUMPRODUCT(($B$2:$B$13=B2)*(D2<$D$2:$D$13))+1 to rank the Students in Ascending Order.

That is the Highest Score has nobody with a Higher score and so Scores a 1 (0+1)

The second highest student only has 1 person above him and so they score a 2 (1+1)etc.

To change the Ranking Order from Ascending to Descending we simply reverse the comparison sign in the counting array

Ascending   : =SUMPRODUCT(($B$2:$B$13=B2)*(D2<$D$2:$D$13))+1

Descending : =SUMPRODUCT(($B$2:$B$13=B2)*(D2>$D$2:$D$13))+1

In the Descending formula the highest Ranked Engineering Student, Bob, will have 3 other Engineering students below him and so scores a 4 (3+1)

The second highest Ranked Engineering Student, Chandoo, will have 2 other Engineering students below him and so scores a 3 (2+1)

You can see how that works by looking at the column G

Adding More Conditions

In the examples above the Ascending and Descending formulas have only applied a single Condition to our Conditional Rank formula.

In our example we required that the student is an Engineering student

=SUMPRODUCT(($B$2:$B$13=B2)*(D2<$D$2:$D$13))+1

The Green array is checking that the array for that cell is, in the example of Row 2, an Engineering Student.

We can add further conditions simply by adding more Conditional Sections to the formula

ie: To Rank Engineering Students from the South we simply add a second Conditional Section to the Sumproduct Formula.

Ascending: =SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13=C2)*(D2<$D$2:$D$13))+1

Descending: =SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13=C2)*(D2>$D$2:$D$13))+1

You can continue to add multiple sections to suit your needs

To Rank Engineering Students from the South named Bob we simply add a second and third Conditional Section to the Sumproduct Formula.

Ascending: =SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13=C2)*($A$2:$A$13=A2)*(D2<$D$2:$D$13))+1

Descending: =SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13=C2)*($A$2:$A$13=A2)*(D2>$D$2:$D$13))+1

Removing Duplicates from the Rankings

If we modify the data a little and accidentally add a few duplicates scores we can see that the Formulas shown above introduce an error

We can see that both Fred and Bob are Engineering students and they both scored 84. The existing function has scored them equally as 1.

We can see that both Chandoo and Danielle are also Engineering students and they both scored 72. The existing function has scored them equally as 2.

Luckily there is a work around for this.

The base formula in our new data set is

=SUMPRODUCT(($B$20:$B$31=B20)*(D20<($D$20:$D$31)))+1

We can modify this to add a small but slightly different value to each row in the Counting Section of the Sumproduct formula

=SUMPRODUCT(($B$20:$B$31=B20)*((D20+ROW()/1000)<($D$20:$D$31+(ROW($I$20:$I$31)/1000))))+1

you can see that the sections highlighted in Green above add a small number based on the Row Number / 1000 to both the Score and the Score Column. This way numbers closer to the bottom of the Table will have a higher chance of getting a lower rank.

If you want the students higher in the list to have a Higher Ranking you can change the logic as such

=SUMPRODUCT(($B$20:$B$31=B20)*((D20+(100-ROW())/1000)<($D$20:$D$31+((100-ROW($I$20:$I$31))/1000))))+1

Just make sure the value 100 is greater than the last Row number of the data

Updating the Formula

In the Introduction I made note that I would bring the Formula into the 21st Century.

The Conditional Rank Formula is based on the use of the Sumproduct Function.

=SUMPRODUCT(($B$2:$B$13=B2)*(D2<$D$2:$D$13))+1

The Sumproduct function must be the most versatile function ever introduced to Excel.

However if you examine the formula you will see it basically says “Sum the number of entries where a Condition is met, But Sum is effectively Counting

So it sort of sounds like a Countifs() Function?

You can examine the Countifs() based equivalent functions below

Ascending version

Sumproduct =SUMPRODUCT(($B$2:$B$13=B2)*(D2<$D$2:$D$13))+1

Countifs =COUNTIFS($B$2:$B$13,B2,$D$2:$D$13,”>”&D2)+1

Descending version

Sumproduct =SUMPRODUCT(($B$2:$B$13=B2)*(D2>$D$2:$D$13))+1

Countifs =COUNTIFS($B$2:$B$13,B2,$D$2:$D$13,”<“&D2)+1

Unfortunately Excel doesn’t allow us to use the F9 evaluate facility on the components of Countifs()

But reading each formula from left to right, they say

Count the cells If, The First Column B is equal to the Rows Value of Column B and the second Column D is greater than the Rows Value of Column D

That is = Countif ( Department Column = Engineering & Score Column > Current Cell)

Closing

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.