How do I get rid of "#DIVISION/0!" in Excel 2003?

How do I get rid of "#DIVISION/0!" in Excel 2003?

I have a column where some cells are not filled in so I get #DIVISION/0!-error for those cells because I have the formula cella/cellb. Do I have to assign a value 1 to get rid of this message?
LVL 1
hermesalphaAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

see attached file. The formula is

=IF(F26>0,G26/F26,"")

copied down. (By the way, my system uses commas where your system uses semicolons. If you copy formulas from this post, you need to replace the commas with semicolons or copy them from the attached worksheet instead).

If the cell in column F contains text, column H will show the "Value#" error. To avoid that, you could use

=IF(AND(ISNUMBER(F26),F26>0),G26/F26,"")

If column F contains a number, the calculation will be performed.
If column F contains no value, the "" (blank value) will be returned.
If column F contains a zero, the "" (blank value) will be returned.

I can not recreate a situation where the "#Name?" error is returned. Can you upload a sample file where that occurs?

cheers, teylyn
Book6.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello, you can use something like=if(cellb>0,cella/cellb,"")cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Or, if cellb can contain negative values=if(cellb<>0,cella/cellb,"")
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jimyXCommented:
=IF(ERROR.TYPE(A1)=2,"Div/0","Not Div/0")

A1 the cell that contain the Div0 error
0
 
cbenCommented:
To cover many errors change formula to:

=IF(ISERROR(old_formula),[value in case of error],old_formula)  

Value in case of error can be more descriptive or "" to show blank.

I recommend ASAP utilities that has a function to re-write the error generating formulae in a sheet very quickly. Trial at http://www.asap-utilities.com/ 
0
 
Ephraim WangoyaCommented:

Just us IF statement to check the value of the item divided
=IF(B1>0, A1/B1, 0)
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
ewangoya,

your suggestion is a duplication of the first comment, just with a slightly different False option.

Please refer to http://www.experts-exchange.com/help.jsp#hs=30&hi=416 to see the guidelines for answering questions.

cheers, teylyn
0
 
Ephraim WangoyaCommented:
@hermesalpha
Ignore my post, similar variant of teylyn's solution
0
 
hermesalphaAuthor Commented:
Hi Teylyn,

If I try your first suggestion, how should I enter the formula? Now, the cell contains =G26/F26. How should I add the formula =if(cellb>0,cella/cellb,"") to this?

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

since you can not divide a number by zero, you must check that F26 is not zero. So,

=if(F26>0,G26/F26,"")

Comparing this with =if(cellb>0,cella/cellb,""), do you see the parallels?

cheers, teylyn
0
 
hermesalphaAuthor Commented:
Hi Teylyn,

In cell H, I wrote like this:

IF(F26>0;G26/F26;"")

There is nothing in F26 (it's empty) so I get this message for the formula:

#NAMN?

("NAMN" means "name")

How can I get the H cell empty if the corresponding F26 cell is empty?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.