Conditional array to calculate percentiles
28,632
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.
Author by
Chris. Z
Updated on June 11, 2020Comments
-
Chris. Z over 3 years
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)!
-
Chris. Z almost 8 yearsIt seems to work, but I should have specified that I'd like it to give me a result in the shape of my table... With values associated to each row. Any smart way to do that?
-
MGP almost 8 yearsSo in your perc column, you want to have the percentile?
-
Chris. Z almost 8 yearsExactly! Small detail, probably, but it would help for what I have to do next (and your formula worked perfectly on the whole dataset btw).
-
MGP almost 8 yearsWhat you could do is use this formula in C2 (assuming you have headers) and drag down :
=PERCENTILE.INC(IF(B:B=B2,A:A),0.05)
-
MGP almost 8 yearsIf you have a lot of data, it could be that the formula will be a bit slow. You can improve the speed by avoiding the full column reference
A:A
andB:B
and replace it by the actual amount of rows you have like this: replaceA:A
withA$2:A$26000
andB:B
withB$2:B$26000
-
Chris. Z almost 8 yearsYes, it's processing like crazy with the A:A and B:B solution... A lot smoother with the actual array. Thank you very much!