Removing the #div/0! from my sheet

[zepan]

Hi Guys and Girls,

I have big sheet with some simple formulas working out the cost price for each unit =SUM(H5)/F5. In some cases the answer is 0 and understand you can't divide 0 by 0 and this is why the remark #DIV/0! comes in the cell. How can I clear this to give me a 0 and not the #div/0! Error.

Thanks in advance,

Matt

 

[Brian from Maui]

Hi Guys and Girls,

I have big sheet with some simple formulas working out the cost price for each unit =SUM(H5)/F5. In some cases the answer is 0 and understand you can't divide 0 by 0 and this is why the remark #DIV/0! comes in the cell. How can I clear this to give me a 0 and not the #div/0! Error.

Thanks in advance,

Matt

Matt,

=IF(F5,H5/F5,0)

 

[NdNoviceHlp]

if(iserror(sum(h5)/f5),0,sum(h5)/f5) or something similiar should do it fo you
edit : syntax

 

[boydr]

Hi Guys and Girls,

I have big sheet with some simple formulas working out the cost price for each unit =SUM(H5)/F5. In some cases the answer is 0 and understand you can't divide 0 by 0 and this is why the remark #DIV/0! comes in the cell. How can I clear this to give me a 0 and not the #div/0! Error.

Thanks in advance,

Matt

This formula will work:
=IF(ISERROR(H5/F5),"0","H5/F5")

 

[applebyd]

Just as a note you're probably better off using Brians
formula rather than the ISERROR. That way
if any other error shows up it will display rather than being surpressed by the command.

HTH

DaveA

 

[damonb]

Hi there,

I also have this problem In two cells but my current formulas are

=SUM(C8)/(C6-C7)

=SUM(C8+C14+C20)/((C6+C12+C18)-(C7+C13+C19))

I have tried to alter this in relation to Brians reply but not very well to be honest. Could someone confirm what this should be please.

Many thanks

 

[Joseph Marc]

Good Afternoon,
I have a similar problem, my formula is =g12/g16/g18*100 and at any time that one of the data is missing from g12 or g16 or g18, the cell has the error #div/0! . I have tried several alteratives but could not be successful, can anyone help?

 

[NdNoviceHlp]

Just thought that this thread with +7000 views must be making many others regret the inane contents. The origineal zepan never did acknowlege the help received. boydr offerred an incorrect solution forgetting about the sum requirement. applebyd was on about some unknown Brian and also doesn't seem to like IsError.

=IF(ISERROR(SUM(H5)/F5),0,SUM(H5)/F5)

was the correct solution.

=IF(ISERROR(SUM(C8)/(C6-C7)),0,SUM(C8)/(C6-C7))

should work for damonb. There's a pattern to these things.
Joseph your aged request will require abit more consideration and perhaps others could come up with a nifty spreadsheet solution. HTH. Closure. Dave

 

[GDCelect]

=IF((((Q12/(G12/E12))-K12)+K12)>E12,"CHK CALC",ROUND((Q12/(G12/E12))-K12,0))

How would this formula be corrected so that the #DIV/O error does not appear?

 

[NdNoviceHlp]

U axd 4 it... =IF(ISERROR(IF((((Q12/(G12/E12))-K12)+K12)>E12,"CHK CALC",ROUND((Q12/(G12/E12))-K12,0))),0,IF((((Q12/(G12/E12))-K12)+K12)>E12,"CHK CALC",ROUND((Q12/(G12/E12))-K12,0)))
HTH.Dave