RANK Function - Zero Value Ranked as 1 - Should be 10

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

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")+COUNTIF($C$9:C9,0)))

HTH,
Bernie
MS Excel MVP


"Sandi" <[email protected]> wrote in message news:[email protected]...
> 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
>

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
news:%[email protected]...
> 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")+COUNTIF($C$9:C9,0)))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Sandi" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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" <[email protected]> wrote in message news:%[email protected]...
> 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
> news:%[email protected]...
>> 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")+COUNTIF($C$9:C9,0)))
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Sandi" <[email protected]> wrote in message news:[email protected]...
>>> 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
>>>
>>
>>
>
>

ahhh...i see!...it tested negative values and it seems to work
fine...however things go wonky when there are less then 10 records. RANK
still shows 10, even if there are less than 10 records.

eg.
COL B...........COL C................COL D
Line 8....NAME...........Volume..............Volume Rank
Line 9....Name1..............45........................2
Line 10...Name2..............0.........................9 (this should show
5 or 4)
Line 11...Name3.............101......................3
Line 12...Name4..............20.......................1
Line 13...Name5..............0........................10 (this should show
5 or 4)

Sandi

>"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>> news:%[email protected]...
>>> 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")+COUNTIF($C$9:C9,0)))
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>>
>>> "Sandi" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

This seems to do what you want: (not tested on negative numbers)

=IF(C9="","",IF(C9<>0,SUMPRODUCT(--(C$9:C$13<>0),--(C9>C$9:C$13))+1,SUMPRODUCT(--(C$9:C$13<>0),--(C9<C$9:C$13))+COUNTIF(C$9:C9,0)))

Biff

"Sandi" <[email protected]> wrote in message
news:[email protected]...
> ahhh...i see!...it tested negative values and it seems to work
> fine...however things go wonky when there are less then 10 records. RANK
> still shows 10, even if there are less than 10 records.
>
> eg.
> COL B...........COL C................COL D
> Line 8....NAME...........Volume..............Volume Rank
> Line 9....Name1..............45........................2
> Line 10...Name2..............0.........................9 (this should
> show 5 or 4)
> Line 11...Name3.............101......................3
> Line 12...Name4..............20.......................1
> Line 13...Name5..............0........................10 (this should show
> 5 or 4)
>
> Sandi
>
>>"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>>news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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
>>> news:%[email protected]...
>>>> 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")+COUNTIF($C$9:C9,0)))
>>>>
>>>> HTH,
>>>> Bernie
>>>> MS Excel MVP
>>>>
>>>>
>>>> "Sandi" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Try...

D9, copied down:

=IF(C9>0,SUMPRODUCT(--($C$9:$C$18>0),--(C9>$C$9:$C$18))+1,(COUNTIF($C$9:$
C$18,">"&C9)+1))

Hope this helps!

In article <[email protected]>,
"Sandi" <[email protected]> wrote:

> 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

Hi Biff ... well, seems to work! i appreciate your time! if you have a
moment, i would really like to break down this formula so i can understand
it.
Sandi


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> This seems to do what you want: (not tested on negative numbers)
>
> =IF(C9="","",IF(C9<>0,SUMPRODUCT(--(C$9:C$13<>0),--(C9>C$9:C$13))+1,SUMPRODUCT(--(C$9:C$13<>0),--(C9<C$9:C$13))+COUNTIF(C$9:C9,0)))
>
> Biff
>
> "Sandi" <[email protected]> wrote in message
> news:[email protected]...
>> ahhh...i see!...it tested negative values and it seems to work
>> fine...however things go wonky when there are less then 10 records. RANK
>> still shows 10, even if there are less than 10 records.
>>
>> eg.
>> COL B...........COL C................COL D
>> Line 8....NAME...........Volume..............Volume Rank
>> Line 9....Name1..............45........................2
>> Line 10...Name2..............0.........................9 (this should
>> show 5 or 4)
>> Line 11...Name3.............101......................3
>> Line 12...Name4..............20.......................1
>> Line 13...Name5..............0........................10 (this should
>> show 5 or 4)
>>
>> Sandi
>>
>>>"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>>>news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> 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
>>>> news:%[email protected]...
>>>>> 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")+COUNTIF($C$9:C9,0)))
>>>>>
>>>>> HTH,
>>>>> Bernie
>>>>> MS Excel MVP
>>>>>
>>>>>
>>>>> "Sandi" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Thanks Domenic! Appreciate your time!
Sandi

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> D9, copied down:
>
> =IF(C9>0,SUMPRODUCT(--($C$9:$C$18>0),--(C9>$C$9:$C$18))+1,(COUNTIF($C$9:$
> C$18,">"&C9)+1))
>
> Hope this helps!
>
> In article <[email protected]>,
> "Sandi" <[email protected]> wrote:
>
>> 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