从一行n个未排序的值中求出(n-6)最小值的平均值的公式

Question

如何使用Excel公式计算一行n个未排序值中最小值的平均值(n-(上限(n*.05))？

我试图得到范围内数值的平均值，去掉最大的5%值，这可以被认为是尖峰。我不想预先排序行，因为我想保持值的顺序。

例如:如果值的范围为D2:Z2，那么我需要C2中的公式来返回(23-(上限(23*.05))的平均值)=范围内的21个最小值。

我正在查看SUMIFS，但无法让它使用标准值中的单元格地址。

Answer 1

这是作为一个具有Shift-Ctrl-Enter的数组公式输入的，工作如下：

=SUM(B1:P1*IF(RANK.EQ(B1:P1,$B$1:$P$1)>6,1))/SUM(IF(RANK.EQ(B1:P1,$B$1:$P$1)>6,1))

假设您在范围内有n=15值，并且您想要(n-6)=9个最小值的平均值，如问题标题中所示。

如果您想要n-(ceiling(n*.05))最小值，那么只需将6替换为CEILING.PRECISE(COUNT($B1:$P1)*0.05)，如下所示：

=SUM(B1:P1*IF(RANK.EQ(B1:P1,$B$1:$P$1)>CEILING.PRECISE(COUNT($B1:$P1)*0.05),1)) 
 /SUM(IF(RANK.EQ(B1:P1,$B$1:$P$1)>CEILING.PRECISE(COUNT($B1:$P1)*0.05),1))

显示中间计算的证明和解释：

​

​

Answer 2

使用了更简单的硬编码特别解决方案。

=(SUM(I2:KV2)-SUM(大(I2:KV2，{1，2，3，4，5，6，7}))/(计数(I2:KV2)-7)