Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi once again to all the Excel experts...

    My problem of the day has to do with the RANK function. I have a user who is using RANK to, well, rank a list of result. Problem is, some of the results show a zero value. The user would like to be able to come up with a result that ignores the zero value. Here is a sample list:

    Values Rank
    17.99 -- 3
    0 -- 1
    15.99 -- 2
    19.99 -- 4
    21.06 -- 5

    User is hoping for:

    17.99 -- 2
    0
    15.99 -- 1
    19.99 -- 3
    21.06 -- 4

    I'm stuck once again, so I'm turning to the real pros.

    Thanks in advance for any assistance...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Say that your list is in A1:A5.
    Enter the following formula in B1:

    =IF(A1=0,"",RANK(A1,$A$1:$A$5,1)-COUNTIF($A$1:$A$5,0))

    Fill down to B5.

    The IF function returns a blank if the value in column A is zero.
    The COUNTIF function is used to subtract the number of zeros from the rank.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769617' date='07-Apr-2009 17:12']Say that your list is in A1:A5.
    Enter the following formula in B1:

    =IF(A1=0,"",RANK(A1,$A$1:$A$5,1)-COUNTIF($A$1:$A$5,0))

    Fill down to B5.

    The IF function returns a blank if the value in column A is zero.
    The COUNTIF function is used to subtract the number of zeros from the rank.[/quote]


    I thought I had tried that very thing, but I must not have, because it works.

    As always, I thank you kindly...

Posting Permissions

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