Return 0 rather than #DIV/O!

[gossv]

Hello, I have another problem!

I am using an "average if" formula and when there isn't a corresponding value it will return a #DIV/O!

I would like to know how to make it return a 0 figure rather than the #DIV/O!. I figure it would be something similar to the "IF ISNA" command

=AVERAGE(IF('MI April - June 2006 (2)'!$K$5:$K$20000=Report!$A30,IF('MI April - June 2006 (2)'!$G$5:$G$20000=Report!F$5,'MI April - June 2006 (2)'!$C$5:$C$20000)))

Thank you for your help.

Ginny

 

[Big Monkey]

Try

=if(iserror(AVERAGE(IF('MI April - June 2006 (2)'!$K$5:$K$20000=Report!$A30,IF('MI April - June 2006 (2)'!$G$5:$G$20000=Report!F$5,'MI April - June 2006 (2)'!$C$5:$C$20000)))), 0, AVERAGE(IF('MI April - June 2006 (2)'!$K$5:$K$20000=Report!$A30,IF('MI April - June 2006 (2)'!$G$5:$G$20000=Report!F$5,'MI April - June 2006 (2)'!$C$5:$C$20000))))

There's probably a better way around this, but it's what I know

 

[gossv]

Thank you big monkey, it works beautifully!

 

[Aladin Akyurek]

Confirmed with control+shift+enter...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF('MI April - June 2006 (2)'!$K$5:$K$20000=Report!$A30,IF('MI April - June 2006 (2)'!$G$5:$G$20000=Report!F$5,'MI April - June 2006 (2)'!$C$5:$C$20000)))))

could be faster.