Results 1 to 6 of 6

rank - exclude zero

This is a discussion on rank - exclude zero within the Excel Questions forums, part of the Question Forums category; I am trying to figure out how to use the rank function on a column of data. The trick is ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    16

    Default rank - exclude zero

    I am trying to figure out how to use the rank function on a column of
    data. The trick is that i dont want it to include or rank zero's. To
    achieve a ranking the number should be a positive number. Here is what
    i have so far.
    For instance if i had a column that was
    32
    0
    14
    55

    14 would have a rank of 1, etc.


    I dont care if it duplicates a ranking as long as the numbers are
    EXACTLY the same. like two items can be ranked 5 if they are both
    $6.06

    =RANK(I5,$I5:$I40,1)&" of "&COUNTIF($I$5:$I$40, ">0")

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,055

    Default Re: rank - exclude zero

    Assuming that A2:A5 contains the data, try...

    B2, copied down:

    =IF(A2>0,RANK(A2,$A$2:$A$5,1)-COUNTIF($A$2:$A$5,"<=0"),"")
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    16

    Default Re: rank - exclude zero

    wow...this board is quick!! love it.

    i tried using a 0 instead of 1 to change the order....and started getting negative rankings. do i need to change the countif statement?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,055

    Default Re: rank - exclude zero

    Try...

    =IF(A2>0,RANK(A2,$A$2:$A$5,0),"")
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    16

    Default Re: rank - exclude zero

    works like a charm. many, many, thanks!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,055

    Default Re: rank - exclude zero

    You're very welcome!
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com