MS Excel中的条件中值 [英] Conditional median in MS Excel

问题描述

我正在计算一个图表的条件中值，如下所示：

I'm trying to calculate the conditional median of a chart that looks like this:

A  |  B
-------
x  |  1
x  |  1
x  |  3
x  |  
y  |  4
z  |  5

我正在使用MS Excel 2007.我知道AVERAGEIF（）语句，但是不等于中位数。主要的技巧是有没有数据的行，例如上面的第四个a。在这种情况下，我不想在计算中考虑这一行。

I'm using MS Excel 2007. I am aware of the AVERAGEIF() statement, but there is no equivalent for Median. The main trick is that there are rows with no data - such as the 4th "a" above. In this case, I don't want this row considered at all in the calculations.

Googling提出以下建议，但Excel不会接受公式格式（也许因为它是2007？）

Googling has suggested the following, but Excel won't accept the formula format (maybe because it's 2007?)

=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)

Excel给出错误说我的错误公式（与条件中的*有关）我也尝试过以下操作，但在计算中将空白单元格计为0：

Excel gives an error saying there is something wrong with my formula(something to do with the * in the condition) I had also tried the following, but it counts blank cells as 0's in the calculations:

=MEDIAN(IF(A:A = "x", B:B, "")

我知道这些公式返回Excel数组，这意味着必须输入Ctrl-shift-enter才能使其正常工作。

I am aware that those formulas return Excel "arrays", which means one must enter "Ctrl-shift-enter" to get it to work correctly.

我可以做条件评估而不考虑空白单元格吗？

How can I do a conditional evaluation and not consider blank cells?

推荐答案

嵌套if语句

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

没有多少解释 - 它检查A是否是x。如果是，它检查B是否不为空。任何符合这两个条件的内容都可以作为中位数的一部分计算。

Not much to explain - it checks if A is x. If it is, it checks if B is non-blank. Anything that matches both conditions gets calculated as part of the median.

给定以下数据集：

A | B
------
x | 
x |     
x | 2
x | 3
x | 4
x | 5

上述公式返回3.5，这是我想要的。

The above formula returns 3.5, which is what I believe you wanted.

这篇关于MS Excel中的条件中值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！