If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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. 
Ads 
#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: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. 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
A formula to AVERAGE IF but only average a set number of values  [email protected]  Excel Worksheet Functions  2  January 31st 08 09:28 PM 
Find monthly average but have average automatically configured  kimbafred  Excel Discussion (Misc queries)  2  August 8th 07 12:28 AM 
Error Handling #N/A with AVERAGE Function  Average of values in Row  Sam via OfficeKB.com  Excel Worksheet Functions  13  July 31st 05 03:59 PM 
Weighed Average of a weiged average when there are blanks  krl  ExcelForums.com  Excel Discussion (Misc queries)  1  July 6th 05 07:37 PM 
how does one convert text to a formula "average(A:A)" to =average(  phshirk  Excel Worksheet Functions  4  April 14th 05 01:20 AM 