Rank consecutively (97+)

Topic

New Reply

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 n-th 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

Reply | Quote

Replies

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 ]

Aladin

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Fred,

The COUNTIF part in the formula in B1 is there just by habit. It’s a tie-breaker 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.

Aladin

Reply | Quote

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 non-parametric 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:

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

Reply | Quote

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

Reply | Quote