Sandi,

RANK doesn't ignore zeroes, so you need to reduce the RANK result by the number of zero values (thus
the first COUNTIF). Then to get the RANK for zero values, you can't use RANK at all, and need to
count the number of non-zero values, and add up any other zeroes in the list.

Of course, this would probably all fall apart if any of your values were negative....

HTH,
Bernie
MS Excel MVP


"Sandi" wrote in message ...
Bernie - thank u so much! it works!
I would love to understand more on how this works if you have a minute!
I'm lost after: IF(B9="","",IF(C9<0,RANK(C9,$C$9:$C$18,TRUE)
Thanks again!
Sandi

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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