Sandi,

=IF(B9="","",IF(C9<0,RANK(C9,$C$9:$C$18,TRUE)-COUNTIF($C$9:$C$18,0),COUNTIF($C$9:$C$18,"<0")+CO UNTIF($C$9:C9,0)))

HTH,
Bernie
MS Excel MVP


"Sandi" wrote in message ...
Hi All - would appreciate your assistance! I am having a problem with how the RANK function ranks
ZERO values.

In the sample below, the VOLUME RANK for NAME2 is 1.0 - since the VOLUME value is Zero (0), i
would like it to read 10.
All the other RANKS are calculating correctly (in ascending order so low numbers are given a high
rank)

The formula in the VOLUME RANK (COL. D) field is: =IF(B9="","",RANK(C9,$C$9:$C$18,1))

COL B...........COL C................COL D
Line 8....NAME...........Volume..............Volume Rank
Line 9....Name1..............25........................ 2
Line 10...Name2..............0......................... 1 (i want this rank to read 10)
Line 11...Name3..............62.......................6
Line 12...Name4..............53.......................4
Line 13...Name5..............67.......................8
Line 14...Name6..............65.......................7
Line 15...Name7..............56.......................5
Line 16...Name8..............109...................10
Line 17...Name9..............96.......................9
Line 18...Name10............30.......................3

Thanks!
Sandi