如何使一个平均公式只计算大于零的数?

=AVERAGE(K2:K32)

=平均(K2:K32)

I have this formula spitting out an average for coulmn K as you can see. The problem I am having is that the numbers in column K are populated by a formula basically adding two other columns together so if no data is entered the number returned is 0. This obviously throws off an average calculation for a month that is not complete, how can I make this formula only count the numbers which are greater than zero?

这个公式给出了库仑K的平均值。我遇到的问题是，K列中的数字是由一个公式填充的，基本上就是把另外两列加在一起，所以如果没有输入数据，返回的数字是0。这显然会使一个月的平均计算无法完成，我怎么能让这个公式只计算大于零的数呢?

3 个解决方案

#1

=AVERAGEIF(K2:K32, ">0")

Here is the example formula, which includes cells having value > 0.

这里有一个示例公式，其中包含有值> 0的单元格。

#2

Since you mention an average calculation for a month that is not complete and that the numbers in column K are populated by a formula basically adding two other columns together if either of the numbers being added could be negative it seems possible that the formula may return 0 even for that part of the month that is complete, so strictly speaking a 0 in that case should be taken into account for a true average. Hence, assuming one of the numbers being added is in ColumnE (which will be blank or 0 for the incomplete part of the month) I suggest an array formula such as:

既然你提到平均每月计算,并不完整,数字列K是由一个公式基本上添加另外两个列在一起如果添加的数字可以是负数似乎可能的公式甚至可能返回0月完成的一部分,所以严格说来0在这种情况下应该考虑为一个真正的平均水平。因此，假设添加的数字中有一个在ColumnE中(这个月的不完整部分将为空或0)，我建议使用一个数组公式，例如:

 =AVERAGE(IF(E2:E32<>0,K2:K32))

entered with Ctrl+Shift+Enter. For months with less than 31 days enter a value other than 0 into ColumnE for the non-existent days.

输入Ctrl + Shift + Enter。对于少于31天的月份，在不存在的日子里，在ColumnE中输入一个不为0的值。

#3

-1

You must use:

你必须使用:

=AVERAGEIF(K2:K32,">0")

Make sure there are no spaces in the formula.

确保公式中没有空格。

#1

=AVERAGEIF(K2:K32, ">0")