Return 0 rather than #DIV/O!

gossv

Board Regular
Joined
Jul 8, 2005
Messages
141
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,136,309
Messages
5,674,999
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top