A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Average with #DIV/0!



 
 
Thread Tools Display Modes
  #1  
Old May 20th 10, 05:46 PM posted to microsoft.public.excel.worksheet.functions
TG Engel
external usenet poster
 
Posts: 1
Default 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  
Old May 20th 10, 05:57 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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  
Old May 20th 10, 06:25 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
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.

  #4  
Old May 20th 10, 09:41 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 35,220
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.