Hacking Excel's Rank Function

Excel's RANK function (RANK.EQ) doesn't give a full ranking of data. It treats duplicates has having the same rank:

Problems with Excel RANK Function

Now, you could use RANK.AVG which would give 6.5 for both cells S12:T12, but it still isn't quite right.

How To Hack the Rank Function in Tables

In Power Excel with MrExcel, Bill Jelen gave me the starting idea for how to tweak Excel's RANK function by counting how many times the value has already occurred and adding it to the rank. But I needed to do it for a table. Here's my solution:

  1. The first cell can use RANK or RANK.EQ (Notice formula) to rank Cell A2 within A2:F14:
    Excel RANK Formula for First Cell
  2. The rest of the first row then needs to check for previous values:
    Excel RANK Hack for first Row
  3. The rest of the first column has to check the previous rows:
    Excel RANK Hack for first Column
  4. The rest of the cells need to check previous rows and cells:
    Excel RANK Hack for Other Cells

Then you get a correct ranking of all values:

Results of Hacking Excel Rank Formulas

Here's My Point

Sometimes Excel Formulas have to be manipulated to provide the right starting point for analysis.
Sometimes you need a simple mathematical formula, sometimes you need something more exotic.

Isn't it Time to Upgrade Your Excel Charting Skills?

Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?

primitive charts
Primitive Charts
smart charts
Smart Charts

Stop Struggling with Excel Charts!
Start creating Smart Charts in just minutes.

Download a free 30-day trial. Get Smart Charts for Excel now!

Smart Charts Included in QI Macros for Excel