View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Teethless mama Teethless mama is offline
external usenet poster
 
Posts: 3,718
Default Average with #DIV/0!

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