Ranking with duplicate values while ignoring zeros and negative values

[mfortier3]

I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it. Any ideas?

Thanks

Please Login or Register  to view this content.

[daddylonglegs]

Try this version

=IF(S32<=0,"N/A",COUNTIFS(S$32:S$38,"<"&S32,S$32:S$38,">0")+COUNTIF(S$32:S32,S32))

[mfortier3]

Thanks daddylonglegs that worked well.

[gvipin]

Try this version

=IF(S32<=0,"N/A",COUNTIFS(S$32:S$38,"<"&S32,S$32:S$38,">0")+COUNTIF(S$32:S32,S32))

such a great formula ...thanks a lot...but what if i want same ranking for duplicate values while ignoring zeros??????