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

[TABLE="width: 712"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1-Jul-11[/TD]
[TD="class: xl66, width: 94, align: right"]-$10,099.94[/TD]
[TD="class: xl66, width: 112, align: right"]-$3,041.88[/TD]
[TD="class: xl66, width: 90, align: right"]-$265.98[/TD]
[TD="class: xl66, width: 69, align: right"]$1,287.10[/TD]
[TD="class: xl66, width: 69, align: right"]-$359.98[/TD]
[TD="class: xl66, width: 69, align: right"]$955.00[/TD]
[TD="class: xl66, width: 69, align: right"]-$234.96[/TD]
[TD="class: xl66, width: 76, align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 342"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]-55[/TD]
[TD="align: right"]-55[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]-55[/TD]
[TD="align: right"]-55[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99999999[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]99999999[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.