# Ranking

Posted by Scott on July 05, 2001 1:57 PM

I need to find a way to apply a rank to a column of sales. The sales data changes regularly, so I need something that will aplly a ranking no matter what order the numbers are in. I used the formula "=RANK", and that seemed to work except if I had rows that were tied (say I had two rows of data that were 1000) they are both ranked the same, then it skips one, and starts with the next highest(say row1=1000 and is ranked 75, row2=1000 and is also ranked 75, row3=1001 and is ranked 77). I need one of the rows that are equal to be 75, and the other 76. Here is an example:

A B
1 1000 3
2 976 2
3 850 1
4 1000 3
5 1050 6
6 1025 5

Formula in B1 is "=RANK(A1,\$A\$1:\$A\$6,1)" and is copied down. Notice B1 and B4 both are ranked 3 and there is no ranking of 4.

Posted by IML on July 05, 2001 2:08 PM

assuming your data is in A1:A6, try the following
=RANK(A1,\$A\$1:\$A\$6,1)+COUNTIF(A1:\$A\$6,A1)-1
and copy down. Be sure to note the the relative A1 in the count if formula.

Special nod to Aladin with further credit to Celia. I couldn't find the original string where Aladin provided this, so he may be able to clean it up a little.
This formula the first occurance will have the higher number rank.

Posted by Scott on July 05, 2001 2:25 PM

--Thanks. This is perfect.

Posted by Scott on July 05, 2001 3:10 PM

But what if there are 3 number which are identical and not just 2?

Posted by IML on July 05, 2001 3:25 PM

It should be fine. Here are my results with modifying your last number to a duplicate (triplicate).

What results are you expecting (if not this)?

Posted by Scott on July 05, 2001 3:37 PM

What I'm trying to do is if there is a duplicate, then leave it blank, and then go to the next higher number and return a result..Example

A1 B1
200......1
110......2
110......
105......3
104......4
104......
104......
101......5

I'm ranking by highest number first...Scott

Posted by Aladin Akyurek on July 05, 2001 3:44 PM

There is a "bug" in Ian's rendition from memory concerning the COUNTIF part. The formula must be:

=RANK(B2,\$B\$2:\$B\$7)+COUNTIF(\$B\$2:B2,B2)-1

I took up your original sample that contained numbers in column B.

Three identical numbers ought not to be a problem.

Posted by Aladin Akyurek on July 05, 2001 4:30 PM

Uniqify then custom rank to meet the new specs

A1 B1

Given the above specs, a different approach may be needed.

I'll assume the following data

{200;110;105;110;104;101;104;104}

in A2:A9. This is an unsorted version of your example.

In B2 array-enter: =SUM(IF(A2&LT;\$A\$2:\$A\$9,1),1) [ copy down up to the last row of data ]

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.

In C2 enter: =IF(ISNUMBER(MATCH(ROW()-ROW(\$B\$2)+1,\$B\$2:\$B\$9,0)),INDEX(\$A\$2:\$A\$9,MATCH(ROW()-ROW(\$B\$2)+1,\$B\$2:\$B\$9,0)),"") [ copy down up to the last row of data ]

In D2 enter: =IF(ISNUMBER(C2),MAX(\$D\$1:D1)+1,"") [ copy down up to the last row of data ]

You'll see the following in A2:D9 including everything:

{200,1,200,1;110,2,110,2;105,4,"","";110,2,105,3;104,5,104,4;101,8,"","";104,5,"","";104,5,101,5}