Excel - Average with #DIV/0!

Asked By TG Engel on 20-May-10 12:46 PM
I am 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

I have gotten around this by using:
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

T. Valko replied to TG Engel on 20-May-10 12:57 PM
Try this array formula** :


** 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.

Microsoft Excel MVP
Teethless mama replied to TG Engel on 20-May-10 01:25 PM

ctrl+shift+enter, not just enter
Dave Peterson replied to TG Engel on 20-May-10 04:41 PM
Another one:

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

is a very large number in scientific format.


Dave Peterson
Ashish Mathur replied to TG Engel on 23-May-10 12:35 AM

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



Ashish Mathur
Microsoft Excel MVP