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.
Aladin
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<$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}
Aladin
Posted by Scott S on July 05, 2001 6:32 PM
Thanks Aladin and IML
Just to avoid confusion, my question was answered on the first try (Aladin's "cleanup" changed the order of duplicates, but in my case, it did not matter). I think there was another "Scott" who was looking for more info here as well. Glad to see my original question, and the subsequent answers helped.
Posted by Scott on July 05, 2001 7:48 PM
Re: Uniqify then custom rank to meet the new specs
I don't know how you did it or what it means, but it works. My list of numbers is already sorted in descending order and your array formula you wrote me works in either case. Thank you and your co-workers for all the help you've given me today...Thanks a bunch, Scott
