É possível média de modo que 0 seja excluído?
Use AVERAGEIF
ou AVERAGEIFS
em vez de AVERAGE
:
Para excluir 0
:
=AVERAGEIF(A1:A50,">0")
Para excluir células em branco:
=AVERAGEIF(A1:A50,"<>""")
Para excluir células em branco 0
e :
=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")
Nota:
-
AVERAGEIF
eAVERAGEIFS
foram introduzidos no Excel 2007 - Para versões anteriores do Excel, consulte Uma média que exclui valores zero
Uma média que exclui valores zero (Excel 2007 e 2010)
Veronica knows how to use the
AVERAGE
function to determine the average of a range of values. She would like to have the average determined based on the non-zero values in the range, however.The worksheet function most suited to this purpose is to use
AVERAGEIF
. You can use it in this manner:=AVERAGEIF(A1:A50,">0")
This function only includes in the average those cells that contain values greater than zero. If you want to also exclude blank cells, you should use the
AVERAGEIFS
function. This function differs fromAVERAGEIF
in that it allows you to specify multiple criteria that indicate which cells to average.=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")
Of course, if you want to approach the problem "old school" (without using
AVERAGEIF
orAVERAGEIFS
), then there are several ways you can proceed. The first is to remember how an average is calculated. It is defined as the sum of a range of values divided by the number of items in the range. Thus, you could figure the exclusionary average by simply making sure that the denominator (the number you are dividing by) does not include any zero values. For instance:=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")
This approach uses the
COUNTIF
function to determine the number of cells in the range(A1:A50)
that don't contain zero. If this range contains not only zeros but also blank cells, and you don't want the blank cells figured into the result, then you need to use a more complex formula:=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)- (COUNTA(A1:A50)-COUNT(A1:A50)))
The
COUNTIF
function counts cells that do not explicitly evaluate to 0, but it will count blank and text cells. TheCOUNTBLANK
term adjusts for the blank cells and the difference betweenCOUNTA
andCOUNT
adjusts the total count for cells that contain text.Of course you can also use an array formula to do your calculation:
=AVERAGE(IF(A1:A50<>0,A1:A50))
Remember that array formulas need to be entered by using the combination Ctrl+Shift+Enter. This array formula also excludes blanks or cells containing text.
All in all it is easier to use the
AVERAGEIF
orAVERAGEIFS
functions.