Ranking while ignoring zeros

[bstarge]

Hoping for the excel gods to answer a question.

I have data in a row like the example posted below. I want to rank the values (including positive/negative numbers), but need to ignore the zero's since a zero indicates no data was present. I can rank including the zeros, but that throws off the ranking.

Thanks,
Ben

1-Jul-11

-$10,099.94

-$3,041.88

-$265.98

$1,287.10

-$359.98

$955.00

-$234.96

$0.00

<tbody>
</tbody>

 

[oldbrewer]

1	44	44	-55	-55
2	-1	-1	-1	-1
3	-55	-55	22	22
4	0	99999999	44	44
5	22	22	55	55
6	55	55	99999999

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

detect zeros and change them to a very large number way past any numbers you could get. Then rank.
Then test numbers and convert any 99999999 to blank cells.