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?

I haven't got XL2007 (AVERAGEIF I believe is an XL2007 function) but ### in a
cell normally indicates the cell is too small (not wide enough) to display
the result; what happens in you widen the column ... is there an error like
#N/A?

"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?

### is also Excel's way of showing negative dates or times if it can't
display them, so it may be sensible to trap for that condition in the
calculation..
--
David Biddulph

"Toppers" wrote in message
...
I haven't got XL2007 (AVERAGEIF I believe is an XL2007 function) but ### in
a
cell normally indicates the cell is too small (not wide enough) to display
the result; what happens in you widen the column ... is there an error
like
#N/A?

"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?

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

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

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