Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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 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

  2. #2
    3 Star Lounger
    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 ]

    Aladin
    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  5. #5
    3 Star Lounger
    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 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
    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    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 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:
    <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>


  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •