+ Reply to Thread
Results 1 to 6 of 6

Rank while ignoring 0 cells when data is greater and less than 0

  1. 03-07-2014, 01:54 PM #1
    david0985
    david0985 is offline
    Registered User
    Join Date
    12-30-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Rank while ignoring 0 cells when data is greater and less than 0

    Hi guys,

    I am looking to rank a range of values in descending order. There are several 0 values in the range, and would like them ignored in the ranking. Usually when ignoring 0s, I can just use countif with the rank function (RANK(A1,A1:A10,1)-COUNTIF(A1:A10,0) for ascending) which just subtracts away the number of 0 cells from the rank. However, this time, my data falls on both sides of zero, so this no longer works. Is there a clever formula workaround for this?

    I have attached a sample spreadsheet.

    Thanks in advance!

    David
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-07-2014, 01:59 PM #2
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Rank while ignoring 0 cells when data is greater and less than 0

    Using your posted workbook...
    this regular formula, copied down, returns the ranks you're looking for:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  3. 03-07-2014, 02:01 PM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,421

    Re: Rank while ignoring 0 cells when data is greater and less than 0

    Try this:

    =IF(A1=0,"",(RANK(A1,A1:A10,1)))

    EDIT: sorry - didn't spot that it needs to be in descending order!
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 03-07-2014, 02:01 PM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Rank while ignoring 0 cells when data is greater and less than 0

    this will give that answer
    =IF(A2<0, RANK(A2,$A$2:$A$11) - COUNTIF($A$2:$A$11,0), IF(A2=0, "", RANK(A2,$A$2:$A$11)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  5. 03-07-2014, 06:53 PM #5
    david0985
    david0985 is offline
    Registered User
    Join Date
    12-30-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Rank while ignoring 0 cells when data is greater and less than 0

    Thanks guys. I actually came up with a differnt solution than both of you, but I think Ron's is the most elegant solution. Thanks!
    Reply With QuoteRegister To Reply
  6. 03-07-2014, 07:34 PM #6
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Rank while ignoring 0 cells when data is greater and less than 0

    blast that damn Ron
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Rank string ignoring blank cells and o value cells
    By BRISBANEBOB in forum Excel General
    Replies: 3
    Last Post: 03-02-2014, 12:56 AM
  2. [SOLVED] Rank function ignoring zero and negative values
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2013, 05:37 PM
  3. Ignoring value if that value is greater than or less than another value
    By cody.starr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2013, 08:12 PM
  4. [SOLVED] counting data ignoring certain cells
    By Red fuji in forum Excel General
    Replies: 16
    Last Post: 06-18-2012, 02:17 PM
  5. Excel 2007 : Excel Rank Function - Ignoring Zero or Blank Cells
    By Mysore in forum Excel General
    Replies: 0
    Last Post: 01-04-2012, 04:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1