# Thread: Exclude zeros using RANK function

1. Hi once again to all the Excel experts...

My problem of the day has to do with the RANK function. I have a user who is using RANK to, well, rank a list of result. Problem is, some of the results show a zero value. The user would like to be able to come up with a result that ignores the zero value. Here is a sample list:

Values Rank
17.99 -- 3
0 -- 1
15.99 -- 2
19.99 -- 4
21.06 -- 5

User is hoping for:

17.99 -- 2
0
15.99 -- 1
19.99 -- 3
21.06 -- 4

I'm stuck once again, so I'm turning to the real pros.

Thanks in advance for any assistance...

2. Say that your list is in A1:A5.
Enter the following formula in B1:

=IF(A1=0,"",RANK(A1,\$A\$1:\$A\$5,1)-COUNTIF(\$A\$1:\$A\$5,0))

Fill down to B5.

The IF function returns a blank if the value in column A is zero.
The COUNTIF function is used to subtract the number of zeros from the rank.

3. [quote name='HansV' post='769617' date='07-Apr-2009 17:12']Say that your list is in A1:A5.
Enter the following formula in B1:

=IF(A1=0,"",RANK(A1,\$A\$1:\$A\$5,1)-COUNTIF(\$A\$1:\$A\$5,0))

Fill down to B5.

The IF function returns a blank if the value in column A is zero.
The COUNTIF function is used to subtract the number of zeros from the rank.[/quote]

I thought I had tried that very thing, but I must not have, because it works.

As always, I thank you kindly...

#### Posting Permissions

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