Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Ranking formula (Excel xp)

    Hi,

    I'm trying to reorder a range of cells in COL C & D based on the numbers in COL D (decending order). These are the numbers in COL D...

    51%
    0%
    23%
    10%
    10%
    3%
    3%
    18%
    31%
    0%
    10%
    3%
    0%
    0%
    0%
    0%
    0%

    i've tried using this formula and am not having good success. what am i doing wrong? thank you for the help.

    ={1+COUNTIF($D$20:$D$36,">"&D20)+SUMPRODUCT(IF($D$ 20:$D$36=D20,1,0))}

  2. #2
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking formula (Excel xp)

    Have you tried the RANK function? This should do the trick. you might need to sort after you use the RANK function. It can also be used rank from the bottom to the top.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Ranking formula (Excel xp)

    Hi jha,

    Where you bin?

    The attached workbook has a set of formulae in column J that show how you can order a set of values in one range according to an order key in another range. If none of the key values repeated, you'd only need the formula in J3. But, since you have repeated values, you need something more sophisticated in the remaining rows, as shown in J4.

    Cheers

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ranking formula (Excel xp)

    <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

    so u missed me macropod well, i've been playing poker lately <img src=/S/compute.gif border=0 alt=compute width=40 height=20> and need the formula to decide when to raise!!!

Posting Permissions

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