• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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?
0
hermesalpha
Asked:
hermesalpha
  • 5
  • 2
  • 2
  • +2
1 Solution
 
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
 
jimyXCommented:
=IF(ERROR.TYPE(A1)=2,"Div/0","Not Div/0")

A1 the cell that contain the Div0 error
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now