# How to rank data

00:00:03,840 --> 00:00:22,250
In this exercise we will rank countries according to how high they score on an alternative HDI index that you created using indicators that you chose from the United Nations data. In this example we've simplified the worksheet a little bit to make it clearer what we're doing.

00:00:22,250 --> 00:00:51,524
In the yellow highlighted columns we created our own HDI from three indicators and average them to create our version of the HDI in the column titled 'Own index'. Let's see how different the country ranking for our HDI is from the official version. First we need to rank the countries in our HDI, in this example we're working only with a subset of 50 countries, but the principle is the same as if we were working with a full list.

00:00:51,524 --> 00:01:34,826
Label a new column as the ranking for your HDI and on the first row type =RANK and then open the bracket. This function has three arguments and it's important to get them right. The first is the easiest: it's the cell that you're ranking and that's the cell to the left. Then type a comma. The second is the list of cells which we're ranking. Select that list by highlighting the cells, then type another comma. The final argument is 0 if you want a descending list - that is the highest number ranks one, or 1 if you want it the other way round.

00:01:34,826 --> 00:02:26,023
We type 0, close the bracket and hit Enter. Then we can apply this to every cell by finding the corner of the cell with the function in it and dragging it down. But we've made an error here and you can clearly see that we have. This list should be a set of numbers between 1 and 50, so what's gone wrong? Well click on the formula again. We want to have a look at the second argument -  the list that we're ranking because we want this to stay the same in all cells, but Excel needs to be told that specifically, and so we need to insert dollar signs before the column and row numbers. So do that, hit Enter, and copy this function down again.

00:02:28,028 --> 00:03:42,445
Now you can see our list is ranked from 1 to 50, but it doesn't look very much like the ranking for the HDI. We can see how close we are in a couple of ways. First of all we can make a new column of the differences in rank. If the rankings were similar these numbers should be quite small and are not. Another way to see how close your index is to the HDI is to make a scatterplot comparing the HDI rank with your rank. To do this we select both columns of rankings hold, the Control key down while you select the second column, then click 'Insert', and locate the scatterplot. Click on that and as you can see our ranking isn't at all similar to the HDI. If it was, the points would cluster to form a straight line sloping upwards and it's pretty fair to say that isn't what we see here.