Cumulative Value Ranking by Percentile

[Apiper]

Hi,

I am working on a spreadsheet which has values attached to customers. I would like to know which values contribute to the top percentages of the cumulative value of the total.
For example if the total value is 300, I would like to know that Customer D and C both have values of 15, therefore they are in the top 10% (30). Where customer A B and E all have values of 10 and are therefore in the 2nd 10% (2nd percentile).
I have tried the percentile rank function but it seems to work on count rather than Sum. I have a spreadsheet of over 15000 like which I need to apply this to.
Any help would be appreciated.

 

[cjms85]

I've created a spreadsheet which I'm pretty sure does what you want.

https://drive.google.com/file/d/0B__bxAaMPpZZT1NoVVJoYXB6cGs/view?usp=sharing

Column B: List of 100 customers with unique names (e.g. Customer 1, Customer 2 etc.
Column C: randomly generated customer number between 1 and 1000.
Column D: Formula which determines which customers have a value greater than or equal to the specified percentile level.
Column E: Array formula, providing a list of the unique names (ignore the first entry "---" as this is just a placeholder I use to show that I cell has a formula in it if it returns a blank").

Cell H2: Cumulative Customer Value = sum of all customer values
Cell H3: Type in any percentile value between 1 and 99
Cell H4: Tells you the customer value for the percentile you have just entered in Cell H3.

Column E is an array formula, so when implementing it in your worksheet, you will have to press CRTL + SHIFT + ENTER. Once you've done that you can drag the formula down.

I've also used a Named Range, that is when you give a range of cells, in this case D3:D102 as CustomerPercentileName. To name a range, select the cells you want to name as a group, then type a name in the box to the left of the one in which formulas appear when you select a cell.

Hope that helps.