Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 10, 05:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
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.

  #2   Report Post  
Old May 20th 10, 05:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old May 20th 10, 06:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old May 20th 10, 09:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
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
  #5   Report Post  
Old May 23rd 10, 05:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:43 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017