Hi,

I need help with ranking data in a group. Is this posibble in excel with or without code. I would like to rank the values within a group of countries for example:

Country Value Rank
US 98 1
US 50 2
US 50 3
US 40 4
NL 100 1
NL 80 2
NL 70 3

How can this be done? I have seen lots of examples with ranking but not in conjunction with a group. All help is welcome.

Rgrds,

Paul.

Paul,

This can be done with Excel sheet forulae but you will need to use array formulae. If your example is contained on a spreadsheet in cells A1 to C8, you are looking to populate cells C2 to C8 with a ranking formula. You should paste into cell C2 the following formula:

The curly brakets denote that this is an array formula, you can't actually type them in but rather you should enter the text between the curly brackets and then confirm the function by pressing Crtl + Shift + ENTER. For more info on array formulae have a look at http://www.cpearson.com/excel/array.htm.

This formula can then be filled down into all all cells below. NB this fomula gives a equal ranking to equal values, this is at odds with your example. You can achieve your example by making the formula slightly more complex and counting back up the list to find previous instances of the same value and adding the count to the ranking.

HTH,
Maccas

Maccas,

This is really rocket science for me, would you be so kind to adjust the array formula in such a way that the ranking is unique per value.

Sorry - was just trying to post at speed. This should be what you're after

I think this will work but is posting on the fly so let me know if its not up to scratch.

NB this is the formula for cell C2 which is to be filled down.

Maccas

Thanks!!! it works. Apparently you're the rocket scientist.!

Hi Maccas,

Unfortunatly this ranking works not as it should be with large data. I have used the function and it appears that in some cases the ranking is not going in a consecutive order this example shows ranking 3 twices within a group:

Sector Origin BU Late (%) rank met count if +1
ABU 0.15 1
ABU 0.13 2
ABU 0.12 3
ABU 0.05 4
BENELUX BU 0.14 1
BENELUX BU 0.13 3
BENELUX BU 0.11 3
BENELUX BU 0.09 4
BENELUX BU 0.09 5
BENELUX BU 0.09 6
BENELUX BU 0.06 7
FRANCE BU 0.10 1
FRANCE BU 0.10 2
FRANCE BU 0.10 3
FRANCE BU 0.10 4
FRANCE BU 0.10 5
FRANCE BU 0.09 9
FRANCE BU 0.09 10
G. CHINA 0.03 1
G. CHINA 0.03 2

Good point - the COUNTIF wasn't specific on category. This one should do you (for the new dataset in cells A2:C21):

This is still an array formula.

Maccas

Top Man!!! do you have a link or a tip where I can find good documentation with respect to this arrays formulas. Because Im still in the dark when it comes down to creating my own.

rgrds,

Paul.

I thought I gave one in my first post: http://www.cpearson.com/excel/array.htm. Let us know if this doesn't do it for you.

In any event, in writing these forumlae you basically undersatnd all there is to know about them. They're very useful when you get your head around where and when they can be used. Be warned that you should always avoid using them if at all possible as they carry a significant calculation overhead penalty (should you be working with big models)

Maccas