Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

@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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.