Thanks:  0
Likes:  0

1. Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ?

Any help would be appreciated. Thanks

2. On 2002-05-07 05:27, swartthydog wrote:
Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ?

Any help would be appreciated. Thanks
=IF(B1,A1/B1,0)

where the denomitor is first tested for being a non-zero number.

3. Try this formula, just substitue A1 & B1 for the relevent cell refs or numbers.

=IF(ISERROR(A1/B1),"0.00",A1/B1)

4. Most probably
List your formula for better help...

=IF(A2=0,0,A1/A2)

Tom

How's that Mark?
_________________
Found a solution? If so, please post again so members of this board can spend their time helping others. Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved". Thanks for being courteous!

[ This Message was edited by: TsTom on 2002-05-08 01:05 ]

5. On 2002-05-07 05:34, TsTom wrote:

=IF(OR(A1=0,A2=0),0,A1/A2)

...If A1 = 0 or A2 = 0 then this formula returns 0.
Why are you concerned with the value of A1?

6. Unless you are sure that only numbers will be put in A1 and A2 it is better to use the ISERROR formula. Otherwise you will get another error message. Eg if you put a number in A1 and Nil in A2 you get the #VALUE! error.

7. On 2002-05-07 08:40, inarbeth wrote:
Unless you are sure that only numbers will be put in A1 and A2 it is better to use the ISERROR formula. Otherwise you will get another error message. Eg if you put a number in A1 and Nil in A2 you get the #VALUE! error.
Not so... If A2 is empty and used in an expression it is treated as 0. It is inadvisable to hide or ignore the presence of an error using the ISERROR function.

8. Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

It is another matter if you want to be notified of some other error.

9. On 2002-05-07 08:50, inarbeth wrote:
Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

It is another matter if you want to be notified of some other error.
I understand the "question" perfectly... and, Aladin's recommended solution...

=IF(B1,A1/B1,0)

...is preferrable to all others.

10. On 2002-05-07 08:56, Mark W. wrote:
On 2002-05-07 08:50, inarbeth wrote:
Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

It is another matter if you want to be notified of some other error.
I understand the "question" perfectly... and, Aladin's recommended solution...

=IF(B1,A1/B1,0)

...is preferrable to all others.
Aladin's formula returns an error message if text is put in A1 or B1. There may be good reasons to want an error message but the ISERROR formula is widely used and may be what swartthydog wants.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•