Results 1 to 7 of 7
Thread: Rank consecutively (97+)

20020210, 16:04 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Rank consecutively (97+)
OK, it's Sunday morning and the brain is foggy.
I have a set of numbers, say
2
5
2
4
4
3
I want to rank them from lowest to highest. Using the RANK formula would give
1
6
1
4
4
3
What I'd like instead is that the rankings be done in groups so that the 2nd smallest set of numbers is given the rank 2, the 3rd small set of numbers is given the rank of 3. RANK accounts for ties among the nth smallest group but gives the next highest group a rank that accounts for all the numbers in the lower groups. What I want for the above data set is
1
4
1
3
3
2
I know how, using an Array Formula, to find how many unique entries there are in the list. Can't go from there to the above ranking.
TIA
Fred

20020210, 21:02 #2
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Rank consecutively (97+)
Fred,
OK, it's Sunday evening over here, so you'll not get a single formula that can resolve the challenging problem you've come up with.
Here we go.
I'll assume that A1:A6 houses your sample data.
In B1 enter: =RANK(A1,$A$1:$A$6,1)+COUNTIF($A$1:A1,A1)1
In B2 enter: =IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",RANK(A2,$A$1: $A$6,1)) [ copy down till B6 ]
In C1 enter: =RANK(B1,$B$1:$B$6,1)
In C2 enter: =IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),VLOOKUP(A2,$A$1: C1,3,0),RANK(B2,$B$1:$B$6,1)) [ copy down till B6 ]
AladinMicrosoft MVP  Excel

20020211, 02:07 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Rank consecutively (97+)
Aladin,
Many thanks. Silly me  wanting a single formula. For my purposes, any number of formulas will do (well not too many). I have about 400 data items. Thanks again  worked like a charm.
Fred

20020211, 03:02 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Rank consecutively (97+)
Aladin,
I've now had a chance to study your solution. I understand how this works. What I don't understand is why you did some of the things you did.
>>In B1 enter: =RANK(A1,$A$1:$A$6,1)+COUNTIF($A$1:A1,A1)1
why is the COUNTIF(...)1 part needed. COUNTIF will always result in the value 1, won't it? So adding 1 and subtracting 1 to the RANK result just gives the same as if we only had RANK. I could understand the COUNTIF being used if this were dragged down, since the arguments $A$1:A1,A1 would change. But subsequent rows in col B don't include a COUNTIF factor. I deleted COUNTIF(...)1 with no apparent adverse effects.
Similarly, why, in the RANK, do you need absolute values for $A$1:$A$6? Again, no dragging, no harm if use relative values.
>>In C1 enter: =RANK(B1,$B$1:$B$6,1)
as above, why absolute values needed for $B$1:$B$6
Thanks again.
Fred

20020211, 05:44 #5
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Rank consecutively (97+)
Fred,
The COUNTIF part in the formula in B1 is there just by habit. It's a tiebreaker and,since it is not needed here (Ties are already handled by the rest of the formulas), it can be dropped.
And, again by habit, the first formulas in B1 and C1 contain frozen ranges. Because these two formulas are not dragged down as you observed, you can unfreeze these two with no adverse effects at all.
AladinMicrosoft MVP  Excel

20020211, 16:04 #6
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Rank consecutively (97+)
Fred,
I don't know if this is important for you, but, the way Excel handles Ranks is statistically not correct. Excel should take care of TIED ranks, because all nonparametric tests (these are hypothesis tests that work with ordinal data, say rank data) make use of tied ranks. Below you can find a small macro that calculateds the ranks of data in a range, taking tied ranks into accounts:
<pre>Function Ranking(R As Range, V As Double) As Double
Dim Nr As Integer
Ranking = Application.WorksheetFunction.Rank(V, R, 1)
Nr = Application.WorksheetFunction.CountIf(R, V)
Ranking = Ranking + (Nr  1) / 2
End Function
</pre>

20020211, 16:36 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Rank consecutively (97+)
Hi Hans,
Turns out this is not important for my current purposes. I just wanted the first group of values (the group having at least one member) to have a rank of 1, the second group to have a rank of 2, etc.
But as far as your input goes, knowing that is still valuable in case I do any parametric modeling. I actually used to dabble in statistics, including a minor in college and a member of the Amer Statistical Assoc for a few years after graduation.
fred