Solved

# PercentRank with dynamic ranges

Posted on 2011-10-10
931 Views
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.
Sample-Percentile-Ranking-Formul.xlsx
0
Question by:dlogan7

LVL 50

Accepted Solution

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

=PERCENTRANK.INC(IF(B\$2:B\$11=B2,C\$2:C\$11),C2)*100

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
27389742.xlsx
0

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.
0

Author Comment

Barry,

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.
0

LVL 50

Expert Comment

Yes, it doesn't need to be sorted

When you do a test like this

IF(B\$2:B\$11=B2,C\$2:C\$11)

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
0

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.
0

## Featured Post

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.