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.

 Average with #DIV/0!
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Average with #DIV/0!

#1
May 20th 10, 05:46 PM posted to microsoft.public.excel.worksheet.functions
 TG Engel external usenet poster 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
May 20th 10, 05:57 PM posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768
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
May 20th 10, 06:25 PM posted to microsoft.public.excel.worksheet.functions
 Teethless mama external usenet poster Posts: 3,718
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
May 20th 10, 09:41 PM posted to microsoft.public.excel.worksheet.functions
 Dave Peterson external usenet poster Posts: 35,220
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 08: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 05:08 PM.