Conditional array to calculate percentiles

Question

I have some data as follows:

    val   crit  perc
0.415605498 1   perc1
0.475426007 1   perc1
0.418621318 1   perc1
0.51608229  1   perc1
0.452307882 1   perc1
0.496691416 1   perc1
0.402689126 1   perc1
0.494381345 1   perc1
0.532406777 1   perc1
0.839352016 2   perc2
0.618221702 2   perc2
0.83947033  2   perc2
0.621734007 2   perc2
0.548656662 2   perc2
0.711919796 2   perc2
0.758178085 2   perc2
0.820954467 2   perc2
0.478645786 2   perc2
0.848323655 2   perc2
0.844986383 2   perc2
0.418155292 2   perc2
1.182637063 3   perc3
1.248876472 3   perc3
1.218368809 3   perc3
0.664934398 3   perc3
0.951692853 3   perc3
0.848111264 3   perc3
0.58887439  3   perc3
0.931530464 3   perc3
0.676314176 3   perc3
1.270797783 3   perc3

I'm trying to use the percentile.inc() function to calculate the 5th percentile for each level of crit (since I have categorized the variable var into classes).

I've tried to use {=PERCENTILE.INC(IF($B$2:$B$32=1,$A$2:$A$32,IF($B$2:$B$32=2,$A$2:$A$32,IF($B$2:$B$32=3,$A$2:$A$32,""))),0.05)} but all it does is calculate the percentile for the whole array and does not give me back the conditional percentiles.

Any help would be most welcome (and FYI, I've got to do this on 26000 rows with 20 levels of crit)!

Answer 1

This worked for me. I have the following layout:

And I used the following formula in G3:

=PERCENTILE.INC(IF(B:B=F3,A:A),0.05)

This is an Array formula, so enter with Ctrl+Shift+Enter. Drag down as suited.