CODEWITHSUNDEEP
excelvba
MonuMan5
MonuMan5

Reputation: 373

Ranking values in Excel columns

Given an Excel file with three columns (similar to this): Group, Number and Rank.

Within the each group he must rank the values from smallest to greatest. If the values match they get the same ranking. So if a value is the largest, the rank would be 1. If it was the second largest, the rank would be 2 and so on.

Then this process should restart for the second set of values. How can this task be accomplished? Maybe using arrays/bubble sort?—but I'm not sure that's the right approach.

Upvotes: 3

Views: 5482

Answers (4)

martin
martin

Reputation: 2638

I solved your problem with one extra column:

In "C" column you have to have the inverse of the number frequency in the given group, using this formula (assuming the values go from row 2 to row 10):

=1/SUM(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2);1;0))

Then in the next column (D) you can get the ranking you need by summing values in C:

=SUM(IF(($B$2:$B$10>B2)*($A$2:$A$10=A2);$C$2:$C$10;0))+1

Both formulas are array formulas (formulae?), you need to press Shift+Ctrl+Enter when entering them in Excel.

Formatted as a table

If you format your data as a table, you can use (in Excel 2010) the following formula:

=1/SUM(IF(([Group]=[@Group])*([Number]=[@Number]);1;0))
=SUM(IF(([Number]>[@Number])*([Group]=[@Group]);[Freq];0))+1

(Depending on your settings, you might have to replace the semicolons with commas.)

Upvotes: 0

Stepan1010
Stepan1010

Reputation: 3136

I think I have the solution you are looking for - I had to do it in three columns though.

Assuming you have the group in column A and the score in volumn B; you are going to want to sort first by group and then by score.

Then you are going to want to go ahead and put the previously mentioned formula in column C2:

=SUMPRODUCT(($A$2:$B$25=A2)*($B$2:$B$25>B2))+1

Then autofill that down.

Then you are going to want to put a counter in cell D2:

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,0,1)

Then autofill that down.

Then you are going to want to put this equation in cell E2:

=IF(C2<>C1,C2-SUMIF($A$2:A2,A2,$D$2:$D$25),C2)

Then autofill that down.

There might be a simpler/better way to do this - but this will definitely give you what you are looking for:

Ranking Solution

Upvotes: 1

Brad
Brad

Reputation: 12253

I did a custom sort with the first level of sort being the group column (smallest to largest) and the second level of sort being the value column (largest to smallest). Then in the rank column I used the formula 

=IF(B2=B1,C1,COUNTIF($A$2:A2,A2))

where A2 is the very first row. Then I dragged it all the way down.

This gives you the rank PLUS everything is visually sorted on the screen as when you're done.

Upvotes: 0

whytheq
whytheq

Reputation: 35607

(Just noticed your comment but already written the following so may aswell upload it as it might be useful for future viewers of this question)

=SUMPRODUCT(($A$2:$A$18=A2)*($B$2:$B$18>B2))+1 .

enter image description here

Upvotes: 0

Related Questions