Conditional array to calculate percentiles

28,632

This worked for me. I have the following layout:

enter image description here

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.

Share:
28,632
Chris. Z
Author by

Chris. Z

Updated on June 11, 2020

Comments

  • Chris. Z
    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
    Chris. Z almost 8 years
    It 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
    MGP almost 8 years
    So in your perc column, you want to have the percentile?
  • Chris. Z
    Chris. Z almost 8 years
    Exactly! 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
    MGP almost 8 years
    What 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
    MGP almost 8 years
    If 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 and B:B and replace it by the actual amount of rows you have like this: replace A:A with A$2:A$26000 and B:B with B$2:B$26000
  • Chris. Z
    Chris. Z almost 8 years
    Yes, it's processing like crazy with the A:A and B:B solution... A lot smoother with the actual array. Thank you very much!