+ Reply to Thread
Results 1 to 2 of 2

Dynamic range to use in percentrank formula

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Dynamic range to use in percentrank formula

    I have a big table where I am trying to calculate percentrank of B in column C for each individual entry in A separately. So based on the example below, "PERCENTRANK("$B$2:$B$10",B2)" for CHI, STL, LOS separately, without having to sort. How can I define a dynamic range in column B based on all =A for that row? So in C2, the formula should pick a range in B that includes values only for A="CHI", but not manually. Is this possible?

    A B C

    CHI 5 Percentrank among CHI values
    CHI 12 Percentrank among CHI values
    STL 1 Percentrank among STL values
    CHI 7 Percentrank among CHI values
    LOS 2 Percentrank among LOS values
    LOS 1 Percentrank among LOS values
    LOS 8 Percentrank among LOS values
    STL 7
    CHI 9

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Dynamic range to use in percentrank formula

    Hi and welcome to the forum

    you can do this with 1 or more helper columns, and then use this for each helper...
    =IF(A2="Chi",B2,"")
    you can then use this for the %rank for each helper...
    =IFERROR(PERCENTRANK($F$2:$F$43,F2),"") (I used F for y helper)

    I am unfamiliar with this function, but also take a look at using this variation, without helpers...
    =PERCENTRANK(IF($A$2:$A$43="Chi",$B$2:$B$43,0),B2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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