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?


--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Chris" wrote:

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.

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

On 13 Sty, 13:46, Chris wrote:
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.

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.

"JBeaucaire" wrote:

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?


--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Chris" wrote:

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.