Rank numbers in list that has duplicates, but exclude zeros

I have a column that can hold 25 numbers. If there are less than 25, the balance are zeros. I want to rank the numbers, not the zeros, even if there are duplicates. Does any one know how? If I could only combine the two formulas below.


This formula works well to rank the numbers greater than zero, but gives the same rank to duplicate numbers. =IF(W7<0, RANK(W7,$W$7:$W$31)-COUNTIF($W$7:$W$31,0), IF(W7=0,"", RANK(W7,$W$7:$W$31)))


This formula ranks every number, even duplicates, but always includes the zeros {=SUM(1*(R7>$R$7:$R$31))+1+IF(ROW(R7)-ROW($R$7)=0,0,SUM(1*(R7=OFFSET($R$7,0,0,INDEX(ROW(R7)-ROW($R$7)+1,1)-1,1))))}

The attached spreadsheet has a column of data on the left. In that column I want to be able to enter from one to twenty five numeric values. I want the column on the right to rank the numbers on the left, but ignore zeros and duplicate values. The attached spreadsheet assigns a unique rank to each value like I want, but doesn't ignore the zeros. Hope this makes sense.

Thank you so much. This will make the spreadsheet so much easier to use.

I just wish I understood more about how it works.

You are right. I did not see the duplicate ranks.