excel - 中值/平均值不返回正确的值

Question

Image for reference



我正在努力实现以下目标:
if(cell A1 is found in list 1), 对于找到它的每一行和if(C4:C10 > B4:B10) ，然后 median(the subtraction between C and B values, for every row that has text1) .

我尝试了两个 2 不同的公式:

1 - {=MEDIAN(IF(AND((C4:C10>B4:B10);(B4:B10=A1));(C4:C10-B4:B10)))}
2 - {=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}
对于中位数，它总是返回 0，而对于平均值，它是不准确的非常小的值。我确定中位数和平均值不正确。

会有什么问题？

另外，我将如何使用类似的东西:{=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}
如果一列在某些行中有文本？ (前一个问题不是这种情况，但它已经出现过)。

text1                   

list 1      list 2      list 3
text2       1           5
text4       2           4
text1       4           6
text4       1           6
text1       4           5
text4       2           4
text1       3           3

Answer 1

您不能使用 AND在这些类型的公式中起作用，因为 AND根据需要返回单个结果( TRUE 或 FALSE )而不是数组。

您的第二个公式更接近，但是通过将所有条件相乘，您将为不满足条件的每一行得到零，从而使结果产生偏差。

您可以使用以下任一类似版本:
=MEDIAN(IF((C4:C10>B4:B10)*(A4:A10=A1);C4:C10-B4:B10))=MEDIAN(IF(C4:C10>B4:B10;IF(A4:A10=A1;C4:C10-B4:B10)))
两者都需要用 CTRL+SHIFT+ENTER 确认

处理列中的文本 B或 C (并使公式忽略这些行，但以其他方式工作)您可以添加额外的 IF像这样的功能
=MEDIAN(IF(C4:C10>B4:B10;IF(A4:A10=A1;IF(ISNUMBER(C4:C10-B4:B10);C4:C10-B4:B10))))AVERAGE 的所有公式都同样适用代替MEDIAN的功能

获取 MEDIAN 的另一种方法而忽略文本是使用AGGREGATE像这样的功能:
=AGGREGATE(17;6;C4:C10-B4:B10/(C4:C10>B4:B10)/(A4:A10=A1);2)
这不需要“数组条目”，但仅适用于 Excel 2010或更高版本。 AVERAGE 没有简单的等价物
17表示 QUARTILE函数 - 第二个四分位数相当于中位数

请参阅随附的屏幕截图，演示最后两个公式与您的示例数据......以及一些添加的文本