Help Needed Percentile rank based off of an array

[drcline87]

Dear All,

I have an interesting thought experiment. I have a log with names, places and scores next to each other:

John | New Jersey | 145.33
Amy | New York | 130.33
Joe | New Jersey | 100.33
Will | New Hampshire | 99.33
Bill | New York | 87.33
etc..

Each "|" represents a new column. Col. A names are unique. Col B names contain matches and Col C contain matches. If I want a nested formula to find the percentile rank of joe's score compared to everyone else in New Jersey, how would I go about doing this? Originally, I was thinking about making an array. I want to avoid restructuring the main table or extracting and making another table. Is there a way to nest a PERCENTRANK.INC to make this work?

Thanks,

Dave

[southward]

Hi,

You can do all kinds of number crunching with a Pivot Table.

Attached is a file with your data and a Pivot Table to the right of it. I added a Slicer to easily change the "place".

Pivot Tables can be very helpful, but require a lot of experimentation and practice to learn the many different ways to display data.

Hope this is helpful.

Cheers