# rank - exclude zero

This is a discussion on rank - exclude zero within the Excel Questions forums, part of the Question Forums category; I am trying to figure out how to use the rank function on a column of data. The trick is ...

1. ## rank - exclude zero

I am trying to figure out how to use the rank function on a column of
data. The trick is that i dont want it to include or rank zero's. To
achieve a ranking the number should be a positive number. Here is what
i have so far.
For instance if i had a column that was
32
0
14
55

14 would have a rank of 1, etc.

I dont care if it duplicates a ranking as long as the numbers are
EXACTLY the same. like two items can be ranked 5 if they are both
\$6.06

=RANK(I5,\$I5:\$I40,1)&" of "&COUNTIF(\$I\$5:\$I\$40, ">0")

2. ## Re: rank - exclude zero

Assuming that A2:A5 contains the data, try...

B2, copied down:

=IF(A2>0,RANK(A2,\$A\$2:\$A\$5,1)-COUNTIF(\$A\$2:\$A\$5,"<=0"),"")

3. ## Re: rank - exclude zero

wow...this board is quick!! love it.

i tried using a 0 instead of 1 to change the order....and started getting negative rankings. do i need to change the countif statement?

4. ## Re: rank - exclude zero

Try...

=IF(A2>0,RANK(A2,\$A\$2:\$A\$5,0),"")

5. ## Re: rank - exclude zero

works like a charm. many, many, thanks!

6. ## Re: rank - exclude zero

You're very welcome!

#### Posting Permissions

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