I don't think so.

Your divisor could still be 0 if there no numbers = 0. And it's my guess that
the reason the OP is seeing the ####'s is because the column is too narrow to
see the error (#DIV/0!).



ilia wrote:

Would this work?

=SUM(IF(myRange=0,myRange,0))/COUNTIF(myRange,"=0")

Enter as array formula.

On Jun 20, 8:37 am, Dave Peterson wrote:
Maybe you can use an =countif() to see that is greater than 0 first.

=if(countif(...)=0,"No data",averageif(...))

Mark wrote:

Using 'AVERAGEIF' to calculate a monthly occurrance to return a numerical
value sometimes returns ### if the event has not happened. When I then
calculate for the complete year this also returns ###. I do not want to keep
trawling thro the spreadsheet to remove the cells which have returned ###.
Is there a way to tell excel to ignore ### cells?

--

Dave Peterson

--

Dave Peterson