Solved

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

Posted on 2010-11-10
11
328 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:teylyn
Comment Utility
Hello, you can use something like=if(cellb>0,cella/cellb,"")cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Or, if cellb can contain negative values=if(cellb<>0,cella/cellb,"")
0
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
=IF(ERROR.TYPE(A1)=2,"Div/0","Not Div/0")

A1 the cell that contain the Div0 error
0
 
LVL 1

Expert Comment

by:cben
Comment Utility
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
Comment Utility

Just us IF statement to check the value of the item divided
=IF(B1>0, A1/B1, 0)
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
@hermesalpha
Ignore my post, similar variant of teylyn's solution
0
 

Author Comment

by:hermesalpha
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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:
teylyn earned 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now