Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-10
11
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 50
ID: 34100813
Hello, you can use something like=if(cellb>0,cella/cellb,"")cheers, teylyn
0
 
LVL 50
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Ephraim Wangoya
ID: 34182259

Just us IF statement to check the value of the item divided
=IF(B1>0, A1/B1, 0)
0
 
LVL 50
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:Ephraim Wangoya
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
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 (Microsoft MVP / EE MVE) earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

609 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