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.

⏱️TIMESTAMPS⏱️
00:00 RANK function in Excel overview
00:52 Using the RANK function in Excel
02:31 RANK vs RANK.EQ vs RANK.AVG
04:42 RANK conditional formatting
05:57 Ranking top 3 results
07:04 Rank from low to high
07:53 RANK function without duplicates
11:31 Percentage rank
13:15 How to sort by rank

What is the difference between the Excel functions RANK, RANK.EQ and RANK.AVG. RANK.EQ gives exactly the same results as the RANK function. 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.

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

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers finance training in various formats: YouTube videos, livestreams, classroom sessions, and webinars. This particular video is an example of #Exceltraining

Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join