Dale Fort Blog Number 58

24 05 2018

How to Make Excel Rank Data

If you are tired or dyslergic or indeed you just can’t face the prospect of ranking a lot of data for your Spearman’s rank Correlation Coefficient test, this is the blog for you.

I have always found it next to impossible to get the ranking of a large’ish data set right first time, meaning I have to go back and work out where it all went wrong.   This is extremely frustrating and on occasion can lead to one’s head exploding.

Here’s a small thing you can do with Excel to both avoid an exploding cranium and take the hard work out of ranking data when you are using statistical tests like Spearman’s Rank Correlation Coefficient or the Mann-Witney U-Test.

Those of you that have tried to use Excel will know that it takes great pleasure in telling you how to do things in a strange language that nobody understands.  It also somehow forgets to tell you about an essential bit of the process that means you very carefully follow the isntructions, press the final button and watch as another box appears and laughs uncontrollably while telling you why it all went wrong in the same incomprehensible language.

This process is no exception but it’s really easy once you know how.

We are going to use the RANK.AVG formula, which ranks the data and averages tied items of data.

This video shows you how to do it:


How to make Excel rank some data

Alternatively, if your internet connection moves slower than lichen, here’s a written version:

How to make Excel rank data without changing the order of the entries in the column.

Make a column headed RANK next to your data column

Click in the top cell, next to the cell with the first piece of data in it

Select RANK.AVG in the statistical functions list

Click on number in the dialogue box

Click on the first number in the data column

Click on Ref in the dialogue box

Select the whole data column (click and drag)

Now, the bit that Excel doesn’t tell you

Put a dollar sign between the letters and numbers in the column location reference

(eg. A$5 – A$24)

Click on order in the dialogue box

Enter 1 to make the data be ranked in ascending order (or leave it as it is to rank in descending order)

Fill down the rank column.

And there it is thrill-seekers.  Keep reading   The Dale Fort Blog for more along these lines and many other lines.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: