Home 
Search 
Today's Posts 
#1




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. 
#2




Average with #DIV/0!
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. 
#3




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. 
#4




Average with #DIV/0!
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 
#5




Average with #DIV/0!
Hi,
You may try this array formula (Ctrl+Shift+Enter) =average(if((isnumber(B8:M8)*(B8:M80)),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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
A formula to AVERAGE IF but only average a set number of values  Excel Worksheet Functions  
Find monthly average but have average automatically configured  Excel Discussion (Misc queries)  
Error Handling #N/A with AVERAGE Function  Average of values in Row  Excel Worksheet Functions  
Weighed Average of a weiged average when there are blanks  Excel Discussion (Misc queries)  
how does one convert text to a formula "average(A:A)" to =average(  Excel Worksheet Functions 