Thread
:
Average with #DIV/0!
View Single Post
#
2
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.
Reply With Quote
T. Valko
View Public Profile
Find all posts by T. Valko