Ranking with duplicates and excluding zero

Hi,

I have a list of values which i need to rank.

Some of the values are duplicates and some are zero.

I am using the following formula for ranking the duplicates:

=RANK(O3,O$3:O$84,0)+COUNTIF(O$3:O3,O3)-1

I want to modify the formula to exclude the zero values...

How do i do that?

Thanks....

Originally Posted by BadlySpelledBuoy

One way:

Formula:

Please Login or Register  to view this content.

BSB

Thanks.... but will this take care of duplicate ranking also?

Originally Posted by BadlySpelledBuoy

One way:

Formula:

Please Login or Register  to view this content.

BSB

I dont think this is the solution....

When copying your code, it merely leaves a blank wherever the value is ZERO is in "O" column..... the remaining numbers are still being ranked from 1 to 82!!

what I mean to say is that if 40 out of the 82 values are ZERO..... there should be ranking only from 1 - 42....

Originally Posted by Navin Agrawal

what I mean to say is that if 40 out of the 82 values are ZERO..... there should be ranking only from 1 - 42....

The best thing to do is attach a sample workbook. Make sure there is just enough data to demonstrate your need. Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
That said this formula may do what you want:

Please Login or Register  to view this content.

Let me know if you have any questions.

Thanks guys, but none of these seem to work....

I am attaching a sample sheet to explain what i mean....

Try pasting this formula in O3 and double clicking the fill handle to copy it down:

Please Login or Register  to view this content.

Let me know if you have any questions.
Edit: If you only want to rank the non-zero values, which looking at R15:R23 doesn't seem likely, use the following:

Please Login or Register  to view this content.

Deleted as a previous posting from kvsrinivasamurthy had correct solution.