Advanced Excel: Pivottable Calculate Percentiles

[SIGE]

Hello,

I have a PT with 2 Row Fields ("Product" & "Customer")
and 1 Column Field (" Net Sales")

I would to calculate the "Percentile (Netsales; 75%)" for each "Product" ...

Anyone an idea on what the calculated field should be? Or another solution ..?

Best Regards, Sige

 

[JamesW]

How do you want to calculate the percentage?

X/Y * 100... What's X and what's Y?

Forgive me if I am misreading your post and you have said this, it's nearly the end of the day!

 

[SIGE]

Mmm,

I want to calculate the 75%th Percentile of "NetSales" for each "Product" ...

(It has nothing to do with percent... it is all about calculating a rank..)

Sige

 

[RoryA]

I do not believe you can do that with a calculated field since you can't use worksheet functions.

 

[JamesW]

Can you not work out the percentile before making it into a pivot, and then add that in?

 

[SIGE]

@Rorya: Even 2010 cannot handle worksheetfunctions in Calcualted fields? Cannot wait for Excel 2025 ...

@JamesW: Sure. It is not that straightforward with a couple hundred products and each having a variable number of customers ... just try, you'll having some Index(match)-ing to do

 

[JamesW]

Haha, that's a minefield I will leave to one of the professionals