Percentile function in pivot table

[youra6]

Hi All,

Since it's pretty much impossible to do percentiles on Pivot Table, I found that this formula almost gets me to my goal:

link here: (http://stackoverflow.com/questions/...ot-table-to-summarise-data-by-sub-populations)

{[FONT=Consolas]=PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)}[/FONT]

but what if I wanted to add some more parameters? The AND function doesn't seem to work here, does it have something to do with the fact that its a array function?

This is what I have, derived from the formula above:

{=PERCENTILE(IF(AND(Table3[Month]=A$246,Table3[Ticket Category]=$M$242,Table3[Ticket Initiation]= $M$243),Table3[TTT Source],""),0.95)}

Thanks!

 

[youra6]

Should have scrolled down on the linked page. The solution is below. hehe...

Here is the solution:

<tbody>
[TD="class: votecell"][/TD]
[TD="class: answercell"]If you need to check a condition for more than one column (a=1 AND b=2) you can expand the array using the * character

[FONT=Consolas]=PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)[/FONT]

[/TD]

</tbody>

Can someone tell me what the "*" does here?

 

[konew1]

Here is my take, prepared before I saw your solution

Excel Workbook
	A	B	C	D	E
4	s	right	0.95	50.1
5
6
7
8
9		ticket category	Ticket initiation	Column3	Column4
10		a	left	23
11		s	right	24
12		d	left	28
13		s	right	33
14		w	left	44
15		s	right	52
16		s	left	69
17		d	right	85
18		a	left	3
Sheet1

In your solution the "*" is simply a multiplier.
In your first attempt you tried AND function that does not work with the arrays. The multiplier achieves the same result that you were trying for.
My method with 2 levels of IF is a bit different but gets the same result

 

[youra6]

Hi, I just wanted to say thanks for your response and your help. Thank you for the explanation as well.