[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# PercentRank with dynamic ranges

Posted on 2011-10-10
Medium Priority
1,797 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:Dale Logan
• 3
• 2

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36945894
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

ID: 36945940
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

ID: 36946799
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

ID: 36947154
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

ID: 36947522
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month18 days, 21 hours left to enroll

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

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