#DIV/0!

swartthydog

New Member
Joined
Apr 23, 2002
Messages
4
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
Try this formula, just substitue A1 & B1 for the relevent cell refs or numbers.

=IF(ISERROR(A1/B1),"0.00",A1/B1)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top