我正在使用averageifs函数,并且在任一条件为真的情况下,我有一列需要计算平均值.
I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true.
我尝试使用OR函数,并尝试了大括号,但都给了我错误.
I have tried using the OR function, and tried the curly brackets, but both give me errors.
= AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"})
=AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"})
还有更多范围,但这些范围的编码很好.
There are more ranges, but the coding for those is fine.
要清楚,如果"I"列包含(具体来说)字母FL, OR 字母IF,我想返回一个平均值.其他任何字母应表示该条目未取平均值.
To be clear, I want to return an average if column "I" contains (specifically) the letters FL, OR the letters IF. Any other letters should mean that entry is not averaged.
TIA!
推荐答案AVERAGEIFS附加条件是布尔AND运算符,它无法执行OR运算.但是,由于您的列不变,因此我们可以自己实现以下目的:
AVERAGEIFS extra criteria is a boolean AND operator, it can't perform OR operations. But since your columns do not change we can somewhat implement this ourselves:
这是使用数组公式的简单方法(输入 ctrl + shift + enter ):
Here is a simple way using an array formula (entered with ctrl + shift + enter):
=AVERAGE(IF((I1:I612="IF")+(I1:I612="FL"),N1:N612))或者,如果您不喜欢使用数组公式,则基本上可以使用SUMPRODUCT和COUNTIF手动完成此操作:
Or if you don't like using array formulas you basically do this manually using SUMPRODUCT and COUNTIF:
=SUMPRODUCT((I1:I612="IF")*(N1:N612)+(I1:I612="FL")*(N1:N612))/(COUNTIF(I1:I612,"IF")+COUNTIF(I1:I612,"FL"))更多推荐
具有或功能的Excel平均值
发布评论