Solved

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

Posted on 2010-11-10
11
350 Views
Last Modified: 2012-06-27
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
Comment
Question by:hermesalpha
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34100813
Hello, you can use something like=if(cellb>0,cella/cellb,"")cheers, teylyn
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34100816
Or, if cellb can contain negative values=if(cellb<>0,cella/cellb,"")
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34100817
=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.

 
LVL 1

Expert Comment

by:cben
ID: 34110950
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34182259

Just us IF statement to check the value of the item divided
=IF(B1>0, A1/B1, 0)
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34182276
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34182331
@hermesalpha
Ignore my post, similar variant of teylyn's solution
0
 

Author Comment

by:hermesalpha
ID: 34356890
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34356936
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
 

Author Comment

by:hermesalpha
ID: 34456075
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34456303
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question