Divide by zero error

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a sheet that I am tracking values from other sheets on. I want to
average those numbers but I get a divide by zero error. How do I ignore the
cell when it has this. I have tried
=AVERAGE(IF((MOD(ROW(C6:C23)-ROW(C6),2)=0)*(C6:C23<>0),C6:C23)) and using
ctrl + shift + enter.
 
Depends on how you want to handle it. The simplest way is to fill in the
missing "value if false" portion of the formula with a 1 or a 0, like so:

=AVERAGE(IF((MOD(ROW(C6:C23)-ROW(C6),2)=0)*(C6:C23<>0),C6:C23,0))

If you have Excel 2007, you could leave that off, if you don't want to
display a zero or 1, and wrap the whole formula in an IFERROR:

=IFERROR(AVERAGE(IF((MOD(ROW(C6:C23)-ROW(C6),2)=0)*(C6:C23<>0),C6:C23)),"")

If you're on Excel 2003 or earlier, let me know and I'll show you had to add
that function into your sheet.

Will that work for you?
 
you're lacking one comma in the formula end

=AVERAGE(IF((MOD(ROW(C6:C23)-ROW(C6),2)=0)*(C6:C23<>0),C6:C23,))

this should do it in my opinion
 
For some reason it is not working.
C6:C26 are = to different cells on different sheets. I want to average that
column. Sometimes the C6:C26 may have the #DIV/0! in 1 or more of those cells.
 
Back
Top