THANKS!: RANK Function - Zero Value Ranked as 1 - Should be 10
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
|