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