Divide by zero error

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

 

[JBeaucaire]

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?

 

[Jarek Kujawa]

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

 

[Chris]

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.