条件数组以计算百分位数 [英] Conditional array to calculate percentiles

问题描述

我有一些数据，如下:

    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

我正在尝试使用percentile.inc()函数为 crit 的每个级别计算第五个百分位数(因为我已将变量 var 归类为类).

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

我尝试使用{=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)}，但是它所做的只是计算整个数组的百分位数，而不会把条件百分位数还给我.

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.

任何帮助都将受到欢迎(FYI，我必须在26000行中使用20级 crit 来做到这一点)！

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

推荐答案

这对我有用.我有以下布局:

This worked for me. I have the following layout:

我在G3中使用了以下公式:

And I used the following formula in G3:

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

这是一个数组公式，因此请输入Ctrl + Shift + Enter. 适当拖动.

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

这篇关于条件数组以计算百分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！