Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So I have a spreadsheet with orders that I have organized into a pivot-table that shows the right things etc.

Where there are currently column totals (summing all the rows before), I want to add something that says where the 20, 50, and 70th percentiles are in dollar terms.

Additionally, for each order I want a percentile ranking displayed (something like a column called order percentile, where the percentile for the order is displayed on the order row). I can manually create this by using =PERCENTRANK.EXC(relevant_range,grand_total_cell) but then this will not change then the pivot-table filter changes. Note: each order is really a grand total of other things

The formula for finding the value at the nth percentile is =Percentile.Exc(a1:a100,n*1/00), but I don't know how to make use of this.

How can I make the pivot-table show the percentile data?

Edit: I think that I may need something like: =PERCENTILE(GETPIVOTDATA(fieldname,range_that_contains_the_amounts,not_sure_what_goes_here),0.9) but I am unsure if that is the case. I would like to work within the pivot-table framework, but I will use something like the above if necessary.

share|improve this question
Downvoter care to comment? – soandos Apr 17 '12 at 19:38

@Soandos, I think you can add three new columns (with 20th, 50th and 70th percentile as headings) to your raw data to capture the 20th, 50th and 70th percentile calculation. Once this is achieved, manually refresh your pivot table to capture these new fields in your Pivot Table Field List.

share|improve this answer

I think you want a Pareto Chart, which is not a built-in function to Excel or Calc from Open Office, but Open Office does allow drop down dialog sorting on column fields in DataPilot® (how about PivotTable®?)

Then you just choose descending or ascending in the Column with the PERCENTRANK(x,y) function.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .