Thread
:
Average with #DIV/0!
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
Posts: 1,766
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 With Quote
Ashish Mathur[_2_]
View Public Profile
Find all posts by Ashish Mathur[_2_]