Average Function

[Steve]

Hi, is it possible to average a set of numbers while ignoring a zero
entry.

So for exmaple the following 3 numbers are 98 92 0 - if i average them
i get 63 as it adds all three numbers and divides by three. But what I
really want is a resuslt of 95 as this would be the average of the
tywo numbers ignoring the zero.

Is that possible

thanks

Steve

 

[Steve]

I have managed to resolve myself so no need to respond...

If anyone is interested, you use the AVERAGEIF function as follows:

=AVERAGEIF(A1:A3, ">0")

Cheers

steve

 

[Steve]

One last question, would anyone know how to output 0 if the three
numbers happened to be 0, 0 and 0 as at the momenet I get a #DIV0
error

Thanks

Steve

 

[Ron Rosenfeld]

One last question, would anyone know how to output 0 if the three
numbers happened to be 0, 0 and 0 as at the momenet I get a #DIV0
error

Thanks

=if(countif(a1:a3,0)=3,0,AVERAGEIF(A1:A3, ">0"))

=iferror(AVERAGEIF(A1:A3, ">0"),0)

 

[joeu2004]

One last question, would anyone know how to output 0
if the three numbers happened to be 0, 0 and 0 as at
the momenet I get a #DIV0 error [....]

=AVERAGEIF(A1:A3, ">0")

Note that you get the #DIV/0 error whenever there are zero values to
average. All of the being zero is only one such condition with that effect.

If you need Excel 2003 compatibility, try:

=IF(COUNTIF(A1:A3,">0")=0,0,AVERAGEIF(A1:A3,">0"))

For Excel 2007 and later:

=IFERROR(AVERAGEIF(A1:A3,">0"),0)

 

[Steve]

Thank you Ron and Joeu2004

Steve

One last question, would anyone know how to output 0
if the three numbers happened to be 0, 0 and 0 as at
the momenet I get a #DIV0 error [....]

=AVERAGEIF(A1:A3, ">0")

Note that you get the #DIV/0 error whenever there are zero values to
average. All of the being zero is only one such condition with that effect.

If you need Excel 2003 compatibility, try:

=IF(COUNTIF(A1:A3,">0")=0,0,AVERAGEIF(A1:A3,">0"))

For Excel 2007 and later:

=IFERROR(AVERAGEIF(A1:A3,">0"),0)