Overlook Divide by zero Error

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

Hi AccAdmin,

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.

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

 

[AccAdmin]

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

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]

Hi AccAdmin,

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(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

 

[AccAdmin]

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/!.

 

[Jan Karel Pieterse]

Hi AccAdmin,

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/!.

Not true. The formula returns a zero for the cell if it contains any error: #Name!, #DIV/0!, #VALUE!, #NUM!, ...
If there is no error, it returns the cell's content(number or text or 0 if empty)

Regards,

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

 

[AccAdmin]

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.

 

[AccAdmin]

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.

 

[Jan Karel Pieterse]

Hi AccAdmin,

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.

OK, let's step back and return to your original post:

"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."

One way is by using this formula for the SUM:

=SUM(IF(NOT(ISERROR(K5:K45)),K5:K45,0)*1)

Enter the formula using control+shift+enter, it is an array formula.

Regards,

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