Dale Logan
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.
Sample-Percentile-Ranking-Formul.xlsx
Sample-Percentile-Ranking-Formul.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER
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.
ASKER