Percentile of order in excel pivot-table
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.
@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.