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

[TABLE="width: 428"]
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Team[/TD]
[TD]Process[/TD]
[TD]Day[/TD]
[TD]Vol[/TD]
[TD]Hrs[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Lift[/TD]
[TD]Monday[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]140.13[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]4232[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]9.75[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Down[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]14343[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]448.22[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Lift[/TD]
[TD]Monday[/TD]
[TD="align: right"]3234[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]5.92[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Lift[/TD]
[TD]Monday[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]0.36[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Tuesdat[/TD]
[TD="align: right"]5454[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]160.41[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]2332[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]5.11[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Monday[/TD]
[TD="align: right"]32432[/TD]
[TD="align: right"]576[/TD]
[TD="align: right"]56.31[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Push[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]4325[/TD]
[TD="align: right"]3435[/TD]
[TD="align: right"]1.26[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Push[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]74.33[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Monday[/TD]
[TD="align: right"]23214[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]539.86[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Lift[/TD]
[TD]Monday[/TD]
[TD="align: right"]3423[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]76.07
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Push[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]34545[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]100.13[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Down[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]3423[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]5.23[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Lift[/TD]
[TD]Monday[/TD]
[TD="align: right"]3565[/TD]
[TD="align: right"]5675[/TD]
[TD="align: right"]0.63[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Push[/TD]
[TD]Monday[/TD]
[TD="align: right"]5654[/TD]
[TD="align: right"]3455[/TD]
[TD="align: right"]1.64[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Down[/TD]
[TD]Tuesdat[/TD]
[TD="align: right"]233434[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]72.43[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.)