来自AVERAGEIFs的AVERAGE，它将忽略AVERAGEIFs哪里是ERROR EXCEL [英] AVERAGE from AVERAGEIFs that will ignore AVERAGEIFs where is ERROR EXCEL

问题描述

我正在尝试从五个AverageIF计算平均值:

I am trying to calculate AVERAGE from five AverageIFs:

AVERAGE(AVERAGEIFs1,AVERAGEIFs2,AVERAGEIFs3,AVERAGEIFs4,AVERAGEIFs5)

在AverageIFs2和AverageIFs4中，

我收到的是#DIV/0！.如何从不仅没有错误的AverageIF中计算AVERAGE?

in AverageIFs2 and AverageIFs4 I am receiving #DIV/0! . How to calculate AVERAGE from AverageIFs that are not have errors only?

在AverageIf之前添加IFERROR，IF(ISERROR)等无法解决问题.

Adding IFERROR, IF(ISERROR) etc before AverageIf is not resolving problem.

推荐答案

这很繁琐，但是您可能需要执行以下操作:

It's rather tedious, but you might have to do something like this:

    =SUM(IFERROR(AVERAGEIFS(A1:A2,A1:A2,">"&0),0),IFERROR(AVERAGEIFS(B1:B2,B1:B2,">"&0),0),IFERROR(AVERAGEIFS(C1:C2,C1:C2,">"&0),0))
/SUM(--ISNUMBER(AVERAGEIFS(A1:A2,A1:A2,">"&0)),--ISNUMBER(AVERAGEIFS(B1:B2,B1:B2,">"&0)),--ISNUMBER(AVERAGEIFS(C1:C2,C1:C2,">"&0)))

编辑

=SUM(IFERROR(AVERAGEIFS(A1:A2,A1:A2,">"&0),0),IFERROR(AVERAGEIFS(B1:B2,B1:B2,">"&0),0),IFERROR(AVERAGEIFS(C1:C2,C1:C2,">"&0),0))
/COUNT(AVERAGEIFS(A1:A2,A1:A2,">"&0),AVERAGEIFS(B1:B2,B1:B2,">"&0),AVERAGEIFS(C1:C2,C1:C2,">"&0))

短一些-Count忽略任何#DIV/0！

is a bit shorter - Count ignores any #DIV/0!

这篇关于来自AVERAGEIFs的AVERAGE，它将忽略AVERAGEIFs哪里是ERROR EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！