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.