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.

Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart

QI Macros add-in for Excel makes creating smart charts a snap.