Change #Div/0 to zero

[netia1128]

#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?

 

[JoeMo]

Take a look at the ISERROR function. Maybe something like:

=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]

This is what I do:

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

Obviously you can put whatever you want for true condition.

 

[tusharm]

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]

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]

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]

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.