Average with #DIV/0!

I'm having a problem with an Average formula. When I use a solution to a
similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the
#DEV/O! errors. However; I do not want to include cells with a zero value.

I have gotten around this by using:
IF(B8:M8=0,"",AVERAGE(IF(B8:M8<>0,B8:M8,"")))
BUT - if there is no value in cell B8 - the formula returns with nothing, if
there is a value in B8, the formula works as expected. I find the very
bizarre.

Try this array formula** :

=AVERAGE(IF(ISNUMBER(B8:M8),IF(B8:M8<>0,B8:M8)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"TG Engel" <TG > wrote in message
...
> I'm having a problem with an Average formula. When I use a solution to a
> similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates
> the
> #DEV/O! errors. However; I do not want to include cells with a zero
> value.
>
> I have gotten around this by using:
> IF(B8:M8=0,"",AVERAGE(IF(B8:M8<>0,B8:M8,"")))
> BUT - if there is no value in cell B8 - the formula returns with nothing,
> if
> there is a value in B8, the formula works as expected. I find the very
> bizarre.

=IF(COUNT(B8:M8),AVERAGE(IF(ISNUMBER(B8:M8),IF(B8: M8<>0,B8:M8))),"")

ctrl+shift+enter, not just enter


"TG Engel" wrote:

> I'm having a problem with an Average formula. When I use a solution to a
> similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the
> #DEV/O! errors. However; I do not want to include cells with a zero value.
>
> I have gotten around this by using:
> IF(B8:M8=0,"",AVERAGE(IF(B8:M8<>0,B8:M8,"")))
> BUT - if there is no value in cell B8 - the formula returns with nothing, if
> there is a value in B8, the formula works as expected. I find the very
> bizarre.

Another one:

=SUMIF(b8:m8,"<"&1E+199) / (COUNT(b8:m8)-COUNTIF(b8:m8,0))

1E+199
is a very large number in scientific format.



TG Engel wrote:
>
> I'm having a problem with an Average formula. When I use a solution to a
> similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the
> #DEV/O! errors. However; I do not want to include cells with a zero value.
>
> I have gotten around this by using:
> IF(B8:M8=0,"",AVERAGE(IF(B8:M8<>0,B8:M8,"")))
> BUT - if there is no value in cell B8 - the formula returns with nothing, if
> there is a value in B8, the formula works as expected. I find the very
> bizarre.

--

Dave Peterson

Hi,

You may try this array formula (Ctrl+Shift+Enter)

=average(if((isnumber(B8:M8)*(B8:M8>0)),B8:M8))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"TG Engel" <TG > wrote in message
...
> I'm having a problem with an Average formula. When I use a solution to a
> similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates
> the
> #DEV/O! errors. However; I do not want to include cells with a zero
> value.
>
> I have gotten around this by using:
> IF(B8:M8=0,"",AVERAGE(IF(B8:M8<>0,B8:M8,"")))
> BUT - if there is no value in cell B8 - the formula returns with nothing,
> if
> there is a value in B8, the formula works as expected. I find the very
> bizarre.