Ranking by Range and not by Absolute Value

I have a list of countries and scores as the below table demonstrates. I would like to rank each of the countries (with a lower score securing a better ranking) but instead of ranking them by their absolute values, I would like to rank them by a specified range. For example, in absolute terms France would be ranked first and Germany would be ranked second. However, by range (0.3 – 0.35) they would both be in joint first position. I would be extremely grateful for any help or formulae that would solve this problem. Thanks!

Argentina 0.5991
Australia 0.4632
Belgium 0.4658
Brazil 0.5751
China 0.3949
Colombia 0.6331
Denmark 0.4733
France 0.3168
Germany 0.3388
Hong Kong 0.4118
India 0.5577
Italy 0.5732
Japan 0.3986
Mexico 0.4701
Netherlands 0.5603
Russia 0.5685
Singapore 0.5142
South Africa 0.5876
South Korea 0.5050
Spain 0.5056
Turkey 0.6724
United Kingdom 0.3727

David

David,

This formula assumes the following:

• There are headers so the first line of data is in row 2
• The scores are in column B
• The range increment is .05 for placement (so 0.3 - 0.35 is ranked 1, 0.35 - 0.4 is ranked 2, etc)

Put this formula in cell C2 and copy down

Please Login or Register  to view this content.

Then just perform a sort

Hope this helps,
~tigeravatar

Thanks this is helpful. I just noticed that with this formula that although France and Germany are now in joint first place the UK is second instead of being in third. Is there another part that I can add to the formula so that excel knows that where two countries are in the same position it will skip a number and go to the next number. Ideally the worst country would get a rank of 22. Here is an example of what I mean from Transparency International
http://www.transparency.org/policy_r...i/2010/results.

Thanks again

David

David,

Ah, I see what you mean. The way I achieved what you're looking for was:
Use the formula provided earlier for column C, and sort by column C
Then, put this formula in cell D2 and copy down, then hide column C

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Tigeravatar, thanks for the suggestion but still doesn't work. Maybe it would be better if I copy and paste the ranks I would like these countries to have. The first column is the country name, the second column is the absolute value, and the third column is the first formula you gave me. The fourth column is me manually typing in the rank that each of these countries should achieve based on whether they fall into a increments of 0.05 range. What would be the formula to achieve this result in the fourth column?

Argentina 0.620681818 7 16
Australia 0.473159091 4 7
Belgium 0.485295455 4 7
Brazil 0.600225 7 16
China 0.412409091 3 4
Colombia 0.670659091 8 21
Denmark 0.503590909 5 10
France 0.317113636 1 1
Germany 0.340545455 1 1
Netherlands 0.430204545 3 4
Hong Kong 0.586409091 6 14
India 0.610272727 7 16
Italy 0.40425 3 4
Japan 0.473666667 4 7
Mexico 0.587727273 6 14
Russia 0.604 7 16
Singapore 0.533795455 5 10
South Africa 0.606952381 7 16
South Korea 0.509575 5 10
Spain 0.523818182 5 10
Turkey 0.704454545 9 22
United Kingdom 0.369613636 2 3

Martin, thanks for suggestion but am looking to rank by range and not by value.

David

Martin, sorry I realized now what you mean. I just plugged that formula in and it works - thanks! Is there a way to combine the rank function with the rounddown formula that tigeravatar gave me so that I can keep all the ranks in one column?

Thanks, this is perfect!