Change #Div/0 to zero

netia1128

New Member
Joined
Sep 14, 2011
Messages
6
#Div/0 is popping up all over my spreadsheets. I know that this is because I'm trying to divide by 0, but is there any way to make excel return the value 0 instead of #Div/0?

All of the solutions I've found so far for this depend upon a formula string such as the following: =IF(denominator=0,"",numerator/denominator). This isn't working for me because (I believe) my denominator is being calculated inside of the formula, so I'm getting something akin to a circular reference error message. Is there any other way around this error code?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Take a look at the ISERROR function. Maybe something like:
Code:
=IF(ISERROR(A1/B1),0,A1/B1)
Assuming you are dividing the contents of A1 by the contents of B1. Adjust the cell refs to suit.
 

jeffreysdunn

New Member
Joined
Nov 13, 2011
Messages
27
This is what I do:

=IF(D9=0,0,N9/D9)

Obviously you can put whatever you want for true condition.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
If all you want to do is hide the error but leave the result of the formula as the error, check out conditional formatting.

If you really want a zero as the result, then if you have Excel 2007 or later, check out the IFERROR function.

If you want to to test the denominator for zero and the denominator is the result of a formula, use =IF(formula-for-denominator = 0, 0, formula-for-numerator / formula-for-denominator).

#Div/0 is popping up all over my spreadsheets. I know that this is because I'm trying to divide by 0, but is there any way to make excel return the value 0 instead of #Div/0?

All of the solutions I've found so far for this depend upon a formula string such as the following: =IF(denominator=0,"",numerator/denominator). This isn't working for me because (I believe) my denominator is being calculated inside of the formula, so I'm getting something akin to a circular reference error message. Is there any other way around this error code?
 

netia1128

New Member
Joined
Sep 14, 2011
Messages
6

ADVERTISEMENT

I'm trying to figure out if that will work, but the formula so far looks like this:

If'Estimate!B8="Sheet",'Materials Usage'!D11/(INDEX('Rate Index'!$S$3:$T$12,MATCH(Estimate!B31,'Rate Index'!$S$3:$S$12,0),2)))*Costs!$M$15+IF(INDEX('Rate Index'!$S$3:$T$12,MATCH(Estimate!B32,'Rate Index'!$S$3:$S$12,0),2)=0,0,'Materials Usage'!D11/(INDEX('Rate Index'!$S$3:$T$12,MATCH(Estimate!B32,'Rate Index'!$S$3:$S$12,0),2)))*Costs!$M$15+IF(INDEX('Rate Index'!$S$3:$T$12,MATCH(Estimate!B33,'Rate Index'!$S$3:$S$12,0),2)=0,0,'Materials Usage'!D11/(INDEX('Rate Index'!$S$3:$T$12,MATCH(Estimate!B33,'Rate Index'!$S$3:$S$12,0),2)))*Costs!$M$15

And it's not going through! I have a feeling I'm not putting the #DIV/0 deleting clause in the right place... can someone either advise at to where in the formula it should go, or tell me what a simpler solution might be?
 

netia1128

New Member
Joined
Sep 14, 2011
Messages
6
Actually, I may as well post the lot of the formulas that are killing me. Also,

=IF(B8="Sheet",'Materials Usage'!D51*('Rate Index'!$X$5/60)*Costs!$M$14,('Materials Usage'!D38/INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B37,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K4,0)))*Costs!$M$14)+INDEX('Roll Load Costs'!$N$2:$P$9,MATCH(Estimate!B37,'Roll Load Costs'!$N$2:$N$9,0),3)

=IF(Estimate!B8="Sheet",'Materials Usage'!D51*('Rate Index'!$X$5/60)*INDEX(Costs!$L$3:$M$27,MATCH(Estimate!B37,Costs!$L$3:$L$27,0),2),'Materials Usage'!D38/INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B37,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K$4,0))*INDEX(Costs!$L$3:$M$27,MATCH(Estimate!B37,Costs!$L$3:$L$27,0),2))

IF(INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B38,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K$4,0))=0,0,IF(Estimate!B8="Sheet",'Materials Usage'!D51*('Rate Index'!$X$5/60)*INDEX(Costs!$L$3:$M$27,MATCH(Estimate!B38,Costs!$L$3:$L$27,0),2),'Materials Usage'!D38/INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B38,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K$4,0))*INDEX(Costs!$L$3:$M$27,MATCH(Estimate!B38,Costs!$L$3:$L$27,0),2)))

Just these four... But they're all popping me back errors every time an index value is zero...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
As suggested, you can wrap the formula expression into an IFEEROR...

=IFERROR(IF(B8="Sheet",'Materials Usage'!D51*('Rate Index'!$X$5/60)*Costs!$M$14,('Materials Usage'!D38/INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B37,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K4,0)))*Costs!$M$14)+INDEX('Roll Load Costs'!$N$2:$P$9,MATCH(Estimate!B37,'Roll Load Costs'!$N$2:$N$9,0),3),0)

If you don't have IFERROR...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,IF(B8="Sheet",'Materials Usage'!D51*('Rate Index'!$X$5/60)*Costs!$M$14,('Materials Usage'!D38/INDEX('Rate Index'!$B$3:$K$22,MATCH(Estimate!B37,'Rate Index'!$C$3:$C$22,0),MATCH(Estimate!B8,'Rate Index'!$B$4:$K4,0)))*Costs!$M$14)+INDEX('Roll Load Costs'!$N$2:$P$9,MATCH(Estimate!B37,'Roll Load Costs'!$N$2:$N$9,0),3)))

I think it's better to avoid megaformulas even just for maintenance reasons.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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