合并Averageif和Averagea：将条件平均值视为0 [英] Combining Averageif and Averagea: take a conditional average treating blanks as 0

问题描述

我需要计算一个可以包含空单元格或空字符串（）的范围的平均值。

公式 AVERAGEIF ，例如 AVERAGE 忽略空单元格。
为了解决这个问题，公式AVERAGE有一个'alter ego'，可以将空单元格和文本转换为0值： AVERAGEA

我没有找到一个连接averageIf和averageA的公式。任何人都有想法解决它？

解决方案

您可以添加0将空字符串和空格转换为0.然后使用 AVERAGEIF 。例子：

$ $ p $ = averageif（arrayformula（A1：A15 + 0），< 3）
code>

计算所有小于3的数字的平均值，将和空格视为0.



这里需要 arrayformula 来处理为范围中的每个元素加0的情况。

I need to calculate an average conditioned over a range that can contain empty cells or the empty strings ("").

The formula AVERAGEIF like AVERAGE ignore the empty cells. For resolve the problem, the formula AVERAGE, has an 'alter ego' that convert empty cells and text in 0 value: AVERAGEA

I don't have found a formula that joins averageIf with averageA. Anyone have an idea to resolve it?

解决方案

You can add 0 to convert empty strings and blank spaces to 0. Then use averageif. Example:

=averageif(arrayformula(A1:A15 + 0), "<3")

calculates the average of all numbers less than 3, treating "" and blanks as 0.

One needs arrayformula here to handle adding 0 to each element of the range.

这篇关于合并Averageif和Averagea：将条件平均值视为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！