Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Completing a Ranking Column (XP SP2)

    Dear Loungers,

    is there an easy way of doing this: In one column I have a calculated priority, the number of rows will vary as new items are added so there isn't a defined range. I want to then calculate the ranking in another column - based on the priority and only for rows that have entries. So it will look like this once the rankings have been added:


    <table border=1><td>Priority</td><td>Rank</td><td>34</td><td>2</td><td>23</td><td>1</td><td>37</td><td>3</td></table>

    Any ideas? thank you.............. liz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Completing a Ranking Column (XP SP2)

    Say that your priorities are in column A, starting in A2.
    Enter the following formula in B2:
    <code>
    =IF(ISBLANK(A2),"",RANK(A2,A:A,1))
    </code>
    and fill down as far as you want.

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Completing a Ranking Column (XP SP2)

    hans,

    I always feel really grateful but immensely stupid when you give me your answers! Why didn't I search help for RANK??? Never having used it and not realising it was there I thought of WOPR. Really I must learn to do the obvious first. Anyway thank god for you and WOPR!

    liz

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Completing a Ranking Column (XP SP2)

    Hans,

    I have tried several things but can't solve this problem:

    If the calculate priority formula has a zero result it means that it is not prioritised (rather than has a zero priority), in this case the zero is "counted" when calculating the rankings, this means all the zero priorities are ranking 1 and the "real" rankings start at 32 (or whatever the next rank is after all the equal ranking priority zeroes). I have tried forcing the priority calculation to populate unprioritised with "" however this still counts when ranking. I also tried populating the unprioritised with a literal since RANK help said it ignores non-numeric, however this produced a #VALUE error. I could populate it with e.g. 999 but would rather have nothing in the cell. Can you think of a way to have nothing in priority and be able to produce a true ranking?

    thank you......... liz

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Completing a Ranking Column (XP SP2)

    When I use a formula that returns <code>""</code> for zero priorities, those values produce an error in the corresponding formula, but they aren't included in the ranking. To suppress the error, you can change the formula in B2 to
    <code>
    =IF(A2="","",RANK(A2,A:A,2))
    </code>
    and fill down.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Completing a Ranking Column (XP SP2)

    Try modifying Hans' formula to:
    =IF(TRIM(A2)="","",RANK(A2,A:A,1))

    Steve

Posting Permissions

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