How to use the RANK function in Excel. Here’s a preview of what we will cover in this Excel
tutorial: How does the RANK function in Excel work;
What is the difference between the Excel functions RANK, RANK.EQ and RANK.AVG;
How to color code your ranked results in Excel so the highest and lowest rank are easier
to recognize; How to rank just the top 3 results;
How to rank in ascending order, from low to high;
How to use a very simple method that I call the “tiebreaker bonus” to avoid duplicates;
and How to evaluate the relative standing of a
value within a data set. Let me introduce the dataset to you. This
is a list of ten YouTube channels in the fields of accounting, finance and investing, sorted
in alphabetical order, with their current number of subscribers.
Let’s rank these channels based on the number of subscribers, in descending order, from
high to low. Let’s put “RANK” as the column header in column D, and press enter.
On the home tab in Excel, go to Functions – More Functions, and type RANK. The dialog
box is going to help us with the function arguments of the RANK function that we insert
in cell D2. We want to rank cell B2, in reference to the list of the numbers of subscribers
in B2 through B11. Let’s lock the column letter in the number field with a $ sign,
and let’s lock both columns and rows in the array, as we don’t want these column
and row references to change when we copy the cell, or copy the whole column. Press
OK, and copy the formula down the column. We have ranked the list of channels from high
(1st place) to low (10th place) based on the number of subscribers. Two channels both rank
at number 8, as they have the same number of subscribers. In case of duplicates, the
RANK function in Excel then skips the next number, in other words there is no 9th place
assigned to anybody. The next channel gets rank number 10. This is OK in some cases,
but in other cases you may not want to have any duplicates, and use a tie-breaker. We
will cover a very easy way to avoid ranking duplicates later in this video.
What is the difference between the Excel functions RANK, RANK.EQ and RANK.AVG. Let’s compare
all three side by side. Let’s put “RANK.EQ” as the column header in column E, and press
enter. On the home tab in Excel, go to Functions – More Functions, type RANK again, and select
RANK.EQ from the list. As before, we want to rank cell B2, in reference to B2 through
B11. Let’s lock the column letter in the number field with a $ sign, and let’s lock
both columns and rows in the array. You can press F4 on your keyboard to insert those
four $ signs in one go. Press OK, and copy the formula down the column. We have ranked
the list of channels from high (1st place) to low (10th place) based on the number of
subscribers. RANK.EQ gives exactly the same results as the RANK function. So is there
any difference between the two? We can find that out if we go back to the function arguments
box. The RANK function is available for compatibility with Excel 2007 and earlier. Someday, the
RANK function might be discontinued in Excel, and RANK.EQ which has exactly the same functionality
will be its successor. Why is it called RANK.EQ? Because RANK.EQ gives duplicate numbers the
same (equal) rank. RANK.AVG handles duplicates differently: if more than one value has the
same rank, the average rank is returned. Let’s see what that looks like. We put “RANK.AVG”
as the column header in column F. Insert the RANK.AVG function in cell F2, for number $B2,
and reference $B$2:$B$11. The two channels with the same rank now each have rank 8.5.
There are no ranks 8 or 9 in this list, but two values of 8.5.
How to color code your ranked results so the highest and lowest rank are easier to recognize.
Let’s hide column E and F, copy over column D into column G, and update the column header
to “Color”. We select cells G2 through G11, go to the Home tab in Excel, and press
Conditional Formatting – New rule. Rule type is “format only top or bottom ranked
values”, then we edit the rule description. Remember that the lowest number (1) is the
highest ranking channel. Format values that are in the bottom 1, click Format, and apply
a green fill. Click OK twice. Continue to select cells G2 through G11, press Conditional
Formatting – New rule. Rule type is “format only top or bottom ranked values”, edit
the rule description. Format values that are in the top 1, click Format, and apply a red
fill. Click OK twice. The highest ranking channel (1st place) is now shown in green,
and the lowest ranking channel (last place) in red.
How to rank just the top 3 results. What if we want to identify the top 3, and have no
need to rank data that is outside the top 3. Put “Top 3” as the column header in
column H, and press enter. Functions – More Functions, IF. We are going to “nest”
the RANK function in here. The logical test is whether the RANK of B2 in the array $B$2:$B$11
is smaller than 4. If that logical test is TRUE, then we want to rank B2 in reference
to cells $B$2:$B$11. If the logical test is FALSE, then return an empty cell: in Excel
language, that is two quotation marks with nothing in between. Press OK, and copy the
formula down the column. Now only the top 3 get a ranking, and everyone else’s ranking
cell is empty. How to rank in ascending order, from low to
high. Let’s put “Smallest” as the column header in column I, and hide columns G and
H. We insert a rank function in cell I2, like we have done before: number is B2, reference
$B$2:$B$11. This time, rather than leaving the order field empty, we type a 1. This will
sort the rank in ascending rather than descending order: from smallest to biggest channel. The
ranking in column I is exactly the opposite of that in column D. Let’s hide column I,
and discuss the most exciting part of this Excel tutorial:
How to avoid duplicates when using the RANK function. We have two rows with the same ranking:
8th place. Then the Excel RANK function skips the next number (there is no 9th place) and
goes straight to 10th place for the next channel in the list. Instead of this, we want the
ranking to be continuous: 8th place, 9th place and 10th place. I have seen many people propose
a solution to deal with duplicates, involving exceedingly difficult nested formulas. There
is a much simpler solution: the “tiebreaker bonus” method. The primary criterion that
we chose to rank YouTube channels is the number of subscribers. If that number of subscribers
is the same for two channels, then we need a secondary criterion to differentiate just
those two channels that are tied for the same place. Let’s unhide column C. Our tiebreaker
will be the latest number of monthly views. Let’s provide each channel with a “tiebreaker
bonus” in column J. We sum the number of views for all ten channels in the dataset.
The tiebreaker bonus for each channel is equal to the number of views for the specific channel
divided by the total number of views for all channels combined. C2/C$12. We want the tiebreaker
bonus to be small, it should be smaller than 1 for each line, as we want to just resolve
duplicates, and not have any channels “jump” a rank in non-duplicate situations. The first
channel in the alphabetical list has 5% of the overall views. The biggest channel in
the list has a staggering 78% of total views! This is called a powerlaw distribution, or
“winner-takes-all” situation. The tiebreaker bonus for each of the two channels that are
tied for 8th place is 0.01, so at first sight it looks like this does not differentiate
them. If we add one decimal, we do see a different value! We call column K the tiebreaker rank,
which we calculate by taking the rank from column D and deducting the tiebreaker bonus
from column J. This calculation increases the rank of each channel. The biggest channel
now has a rank of 0.222, and the channels that were previously tied for 8th place now
rank at 7.985 and 7.992 respectively. Now these tiebreaker rankings in column K are
not going to make sense to anyone that you share the rankings with. Let’s calculate
the final rank in column L, by ranking the ranking of column K. Yes, you heard it right,
we rank the ranking. Insert a RANK function in cell L2. Let’s rank cell K2 in the array
$K$2:$K$11, with order value 1. Copy the formula down the column. The channel near the top
of the list that previously had a shared rank of 8, is now the sole ranker in 8th place.
The channel at the bottom of the list that previously had a shared rank of 8, now ranks
in 9th place. Duplicates resolved! For report-out purposes, it is probably better to hide column
J and K, so users just see the original rank and the final rank, and can easily understand
that we get there by using subscribers as the primary, and number of monthly views as
the secondary criterion. How to evaluate the relative standing of a
value within a data set. Let’s name column M “%-RANK”, and hide columns C and L.
Functions – More Functions, type RANK, and select PERCENTRANK from the list. We want
to look at array $B$2:$B$11, for value (X) B2. We get the percentage ranking, and copy
the formula down the column. Let’s make sense of these results. The biggest channel
has a percentage ranking of 100%, it is bigger than any of the other channels in the list.
The smallest channel has a percentage ranking of 0%, it is smaller than any of the other
channels in the list. How about this channel over here? It is ranking in second place,
so 1 channel is bigger, and 8 channels are smaller. The calculation of percentage ranking
is the number of channels that are smaller (8) divided by the total number of channels
that we compare this channel to (9): outcome 89%. What about the percentage rank for the
Finance Storyteller channel? It is currently ranking in sixth place, so 5 channels are
bigger, and 4 channels are smaller. The percentage ranking is the number of channels that are
smaller (4) divided by the total number of channels that we compare this channel to (9):
outcome 44%. The PERCENTRANK function in Excel looks at the number of channels in the dataset,
not at the gap between the various numbers of subscriber levels.
Let’s hide all the columns that we used for the various calculations, and sort the
dataset by rank. Select the data table, go to the Data tab, Sort, sort by rank, from
smallest to largest. Such is the beauty and magic of using the
RANK function in Excel!