Percentile with Several Conditionals

[Aldonin]

Hi all experts.


I have some data in the following way for which I'd need to get some percentiles:


I'd like a formula which could help me get the .5 percentile of the Rates (Column F) only of those which meet the following criteria: Process = Lift (Column B), Day = Tuesday, I know I have to use an array formula but have been failing badly to manage this.
Could you give me a hand? Thanks a lot!


A B C D E F

Team	Process	Day	Vol	Hrs	Rate
A	Lift	Monday	3223	23	140.13
C	Push	Tuesday	4232	434	9.75
E	Down	Tuesday	14343	32	448.22
A	Lift	Monday	3234	546	5.92
B	Lift	Monday	234	657	0.36
C	Push	Tuesdat	5454	34	160.41
C	Push	Tuesday	2332	456	5.11
C	Push	Monday	32432	576	56.31
D	Push	Tuesday	4325	3435	1.26
D	Push	Tuesday	223	3	74.33
C	Push	Monday	23214	43	539.86
A	Lift	Monday	3423	45	76.07
D	Push	Tuesday	34545	345	100.13
E	Down	Tuesday	3423	654	5.23
B	Lift	Monday	3565	5675	0.63
C	Push	Monday	5654	3455	1.64
E	Down	Tuesdat	233434	3223	72.43

<colgroup><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(PERCENTILE(IF(B$2:B$18="lift",IF($C$2:$C$18="monday",$F$2:$F$18)),0.5),"not available")

 

[Aldonin]

Thanks a lot for that Aladin: quick question, given that my table is quite huge in size, would it be more efficient tu use if and AND instead of nested ifs?
thanks!

 

[Aladin Akyurek]

Thanks a lot for that Aladin: quick question, given that my table is quite huge in size, would it be more efficient tu use if and AND instead of nested ifs?
thanks!

If AGGREGATE function is available on your system, you might want try instead:

=AGGREGATE(16,6,$F$2:$F$18/($B$2:$B$18="lift")/($C$2:$C$18="Monday"),0.5)

which requires just enter. (I'd be interested to hear whether this formula has a better temporal score.)