Overlook Divide by zero Error

  • Thread starter Thread starter AccAdmin
  • Start date Start date
A

AccAdmin

I created a work book with reference cells that have dependencies on other
cells, until these other cells have a value placed in them my reference cell
returns the #DIV/! error, this isn't the problem (or maybe it is) I have also
created a summary sheet that pulls information from each individual sheets in
the work book into a column, once the information is pulled into the column
it calculates a total at the bottom of that column, problem is if a value on
one of the sheets is not returning a number and I end up pulling the #DIV/!
Error, my column does not calculate. How can I have my AutoSum include an IF
scenario something like =IF K5:K45 returns error #DIV/! use 0. Or have each
cell in the column convert in the same manner before it pulls the information
over into the summary sheet.
 
Jan Karel Pieterse said:
Hi AccAdmin,


Suppose you're dividing A1 by B1:

=IF(OR(B1="",B1=0),"",A1/B1)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Jan Karel Pieterse
Click to expand...

Thank you for your reply, I don't think my post was clear enough. Here is a
brief example; book1, lets say I have two sheets set up one named Sheet1:
Summary and the other Sheet2: Mobilization (sheet 2 being the source of
information). In sheet 2 I have a cell say H115 displaying the error #DIV/!
and I want this information from cell H115 pulled into the summary sheet into
cell F13, when the information is pulled into the summary sheet I want cell
F13 to determine if the source cell has a numerical value or displays #DIV/!,
if it displays a number, bring that number into the summary sheet, if it
displays #DIV/! error convert to 0 and bring over into summary sheet.
 
Jan Karel Pieterse said:
Hi AccAdmin,


=IF(ISERROR(Mobilization!H115),0,Mobilization!H115)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Your formula changed the #DIV/! error to a blank cell which would be great if the source cell had #DIV/! in it, the problem is the source cell has a value in it that I want to pull over. The only time I want it to convert to 0 is when there is no numerical value in the source cell only #DIV/!.
Click to expand...
 
Jan Karel Pieterse;

I tried the formula several times, it does not bring over the value
referenced in the source cell, or it is not showing it in the cell, I have
other formulas referencing this particular cell to show the difference
between cost and profit, in this other cell I am seeing a negative value;
which in turn tells me the cell I am placing the formula in is not bringing
over the required information.

PS Thank You for your time in helping me resolve this problem.
 
I ended up opening up a new sheet and trying the formula, it worked fine in
the new sheet. I went back into the original work book and found that the '
' were missing to tell the formula to look at another sheet in the work book.
Thank you for all your help.
 
Back
Top