+ Reply to Thread
Results 1 to 2 of 2

PERCENTRANK formula to exclude zeros?

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    florida
    MS-Off Ver
    mac 2008
    Posts
    63

    PERCENTRANK formula to exclude zeros?

    Hey everyone, I have tried to get this damn formula to work for awhile. I have tried various array formulas and conditional formulas, but I cannot find a way to make a percent rank formula which does NOT take into account zeros. For example:
    100
    200
    0
    0
    300
    400
    500
    0
    0
    600

    If I do a percent rank on this row searching for 200, I want it to return 20% percentrank. However, currently when i do it it returns 56% because it is counting the zeros. any ideas? thanks everyone.

  2. #2
    Registered User
    Join Date
    03-16-2012
    Location
    florida
    MS-Off Ver
    mac 2008
    Posts
    63

    Re: PERCENTRANK formula to exclude zeros?

    got it!
    =PERCENTRANK(IF(C4:C129<>0,C4:C129),O30)
    array, control shift enter when submitting, i just made an error before. replace c4:c129 with your range, o30 with your control

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Min value to exclude zeros
    By jaarthur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 03:34 PM
  2. [SOLVED] Formula needs also to exclude zeros
    By Belsi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-16-2012, 03:03 AM
  3. Exclude zeros in a pivot top 10
    By cosmo6 in forum Excel General
    Replies: 3
    Last Post: 03-20-2009, 07:09 AM
  4. SUBTOTAL - exclude zeros?
    By john_t_h in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2007, 08:58 PM
  5. [SOLVED] Calc RSQ and exclude zeros
    By Bruce in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-19-2006, 09:10 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1