计算Excel 2010中的百分位数 [英] Calculate Percentile in Excel 2010

问题描述

这是我的以下列表.我正在尝试计算95％的呼叫在几毫秒内回来.

This is my below list. I am trying to calculate 95% of calls came back in how many millseconds.

Milliseconds   Number of Calls
    45         14
    46         33
    47         40
    48         41
    49         83
    50         114
    51         124
    52         82
    53         89
    54         99
    55         82
    56         72
    57         80
    58         101
    59         73
    60         74
    61         81
    62         64
    63         70
    64         61

以上数据意味着什么-

14 calls came back in 45 milliseconds
33 calls came back in 46 milliseconds
40 calls came back in 47 milliseconds
etc etc

现在我应该从上述数据中找到95% percentile.表示有95％的时间在这毫秒内回复了电话.

Now I am supposed to find out 95% percentile from the above data. Meaning 95% of time, calls came back in this milliseconds.

有人可以告诉我如何在Excel工作表中执行此操作吗?感谢您的帮助

Can anyone tell me how to do this in Excel Sheet? Thanks for the help

我正在使用Excel2010.我已经复制了Excel工作表中的两列，以计算百分比.

I am using Excel 2010. I have copied both the columns in my Excel Sheet as it is to calculate the percentage.

更新:-

在下面的列表中，我得到的95 percentil e是66.所以这意味着95％的时间，电话在66 milliseconds中返回，我猜这是不对的.在我看来，有95％的时间是在大约4毫秒内回电.

With the below list, I am getting 95 percentile as 66. So that means 95% of time, calls came back in 66 milliseconds which is not right I guess. It looks to me 95% of time, calls came back in ~4ms.

Milliseconds    Number of calls
    0           11
    1           259
    2           504
    3           293
    4           38
    5           15
    6           1
    7           4
    8           1
    9           1
    10          1
    11          2
    23          1
    30          1
    39          1
    147         1

我正在使用此公式-

=PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(B$2:B$21))))<=B$2:B$21,A$2:A$21),0.95)

推荐答案

额外的一列将简化计算，但是您可以在不需要的情况下进行计算……

An extra column would simplify the calculations but you can calculate without if you want......

假设A2:A21中的毫秒数和B2:B21中的呼叫数，您可以使用此数组公式

Assuming milliseconds in A2:A21 and number of calls in B2:B21 you can use this array formula

=PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(B$2:B$21))))<=B$2:B$21,A$2:A$21),0.95)

已通过 CTRL + SHIFT + ENTER

或此非数组版本

=LOOKUP(SUM(B$2:B$21)*0.95,SUBTOTAL(9,OFFSET(B$1,0,0,ROW(B$2:B$21)-ROW(B$2)+1)),A$2:A$21)

两者的结果均为63-更改为0.75(第75个百分位数)，则您得到59

I get a result of 63 with both - change to 0.75 (75th percentile) and you get 59

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