+ Reply to Thread
Results 1 to 5 of 5

Percentrank With an If Statement

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    53

    Percentrank With an If Statement

    Hi Fellas,

    Been a while and glad to be back on the forums (unfortunately have not done a lot of excel recently )

    I have two columns with the following data set:
    Column A Column B Column C
    0 11.5
    0 13.5
    0 12.2
    1 14.0
    1 7
    1 12
    0 2
    0 3
    0 9.6
    1 2.3

    I want to run a percent rank function in column C where I am only calculating the percent rank on numbers which have a 1 associated with them in Column A. So for example, the first number 11.5 would have a zero next to it in column C... all of the numbers until the fourth number would have a zero next to them. Then the 4th number would have a value of 1 (because its the largest number)... the last number (2.3) would have a value of 0 as its in the zero percentile. The numbers in between would correspond to the percentile in which they lie (i.e. numbers 7 and 12, roughly .33 and .66 respectively). Any thoughts for how to do this in Column C?

    Thanks!

    Larry

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Percentrank With an If Statement

    like this
    in C1 copy paste below then hold control and shift together then hold control and shift together and then hit enter to make it array formula and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,132

    Re: Percentrank With an If Statement

    with a helper column and non array formula

    C1: =IF(A1=1,B1,"")
    D1: =IFERROR(RANK.EQ(IF(A1=1,B1,""),$C$1:$C$10,1)/(COUNT($C$1:$C$10)-1)-1/(COUNT($C$1:$C$10)-1),"")

    C1:D1 copied down
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Percentrank With an If Statement

    Quote Originally Posted by hemesh View Post
    like this
    in C1 copy paste below then hold control and shift together then hold control and shift together and then hit enter to make it array formula and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks Hemesh - for some reason it counts the final value as not 1 (but a smaller number say 0.9115) using this approach. I tried changing the formula to percentrank.inc and still same result. Any thoughts?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,873

    Re: Percentrank With an If Statement

    Two thoughts
    1. Try the following array entered formula*: =IF(a1=0,"",PERCENTRANK.INC(IF(A$1:A$10=1,B$1:B$10),B1))
    2. PERCENTRANK.INC was introduced in the 2010 version of Excel (please update your profile)
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. PERCENTRANK plus IF
    By longhorn94 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2018, 01:45 AM
  2. Percentrank Question
    By steveal in forum Excel General
    Replies: 1
    Last Post: 02-17-2016, 01:49 PM
  3. Sumproduct with percentrank
    By MrDonkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-31-2014, 12:04 PM
  4. Percentrank gives me #N/A
    By CzechCzar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 05:38 PM
  5. Using PERCENTRANK in a UDF
    By steakbro in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2012, 02:03 AM
  6. Percentrank does not always return a 1
    By lady_Jane in forum Excel General
    Replies: 8
    Last Post: 07-20-2011, 12:03 PM
  7. Percentrank
    By harishs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-20-2008, 07:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1