can you tell excel to ignore #### cells when calculating

[Guest]

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?

 

[Guest]

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?

 

[David Biddulph]

### 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..

 

[Dave Peterson]

Maybe you can use an =countif() to see that is greater than 0 first.

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

 

[ilia]

Would this work?

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

Enter as array formula.

 

[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!).