Thread: Completing a Ranking Column (XP SP2)

1. 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. 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. 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. 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. 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. 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
•