Link to home
Start Free TrialLog in
Avatar of Dale Logan
Dale LoganFlag for United States of America

asked on

PercentRank with dynamic ranges

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.
User generated imageSample-Percentile-Ranking-Formul.xlsx
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale Logan

ASKER

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