PercentRank with dynamic ranges

Posted on 2011-10-10
Last Modified: 2012-05-12
I am wondering if it's possible to use the PercentRank formula and have the array section be dynamic. I am needing to rank Locations within each Team. The data is linked to an Access file, so it will be changing. In this example, the first 4 rows represent an entire team. The data will always be sorted by TeamCode.
Question by:dlogan7
    LVL 50

    Accepted Solution

    You can use an "array formula" like this in D2


    confirmed with CTRL+SHIFT+ENTER and copied down

    see attached

    If you haven't "array entered" formulas before here's how:

    Paste or type formula in cell - press F2 key to select formula then hold down both CTRL and SHIFT keys and hit ENTER. Curly braces will appear around the formula in the formula bar. You only need to do this for the first formula. Copy down for the rest of the range. If you need to edit the formulas you need to use "CSE" again

    regards, barry

    Author Comment

    Wow. That sounds pretty awesome. Curveball: what if the number of rows changes in the table? Actually, I've seen formulas pointing to tables that use the column names. Maybe that would take care of the number of rows issue.

    Author Comment


    I've tested your formula and it's working flawlessly. This is going to be a life saver. It even looks like the variable column does not even need to be sorted. Would you agree?

    Also, I was just wondering what the logic is doing here: IF(B$2:B$11=B2,C$2:C$11)

    From an IF statement perspective, isn't this always going to be true? Maybe I'm missing something because it's an array.
    LVL 50

    Expert Comment

    by:barry houdini
    Yes, it doesn't need to be sorted

    When you do a test like this


    an array is returned, so for every value in C2:C11 that matches C2 the value of B2:B11 is returned in the array (otherwise FALSE), so that gives you the values you need for the PERCENTRANK function to work on (FALSE values are ignored).

    You can use the whole column but that'll make the formula slow - another way is to define some "dynamic ranges".

    regards, barry

    Author Comment

    I tried it using the column name in the table and it's working just fine. I guess I will just need to check it the next time the number of records in the table changes. Thank you very much. This is going to save a ton of time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now