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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
 
### 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..
 
Maybe you can use an =countif() to see that is greater than 0 first.

=if(countif(...)=0,"No data",averageif(...))
 
Would this work?

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

Enter as array formula.
 
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!).
 
Back
Top