#DIV/0!

[swartthydog]

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

 

[Mark W.]

On 2002-05-07 09:25, inarbeth wrote:

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.

Why would anyone perform arithmetic operations on text such as "cat" and "dog"? What is "cat"/"dog"? Kibble?

 

[inarbeth]

I did not mention cat or dog, so please don't patronise me. I don't know why you are so opposed to the ISERROR formula: it is one way of avoiding an error message. You say tomato and I say tomato. John Walkenbach's Excel 2000 Bible suggests it at page 221 so it has a decent provenance.

 

[Mark W.]

On 2002-05-07 13:50, inarbeth wrote:
I did not mention cat or dog, so please don't patronise me. I don't know why you are so opposed to the ISERROR formula: it is one way of avoiding an error message. You say tomato and I say tomato. John Walkenbach's Excel 2000 Bible suggests it at page 221 so it has a decent provenance.

Sorry, if you feel patronized... I have an innate aversion to bad advice!

I believe in addressing the root cause of a problem... not simply attending to its symptoms. The original request was to "fix" #DIV/0! error. That is best addressed by testing the denominator (only) using the formula...

=IF(B1,A1/B1,0)

You seem intent on addressing an unrelated data type issue. If there's a possibility of using an arithmetic operator on entered text values then it's best to use Data Validation to prevent their entry. An ounce of prevention is worth a pound of cure. And, speaking of pounds -- ISERROR is a 2,000 pound (907.184619497623 kg) gorilla. If you check the Excel Help Topic for this function you'll see that it returns TRUE for EVERY error value...

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

So when you use...

=IF(ISERROR(expression),0,expression)

...you lose all visiblity to underlying problems... it's like prying your eyes out!!! ...And, is very poor worksheet design regardless of whose Bible you quote.
This message was edited by Mark W. on 2002-05-07 15:18

 

[brettvba]

I agree with mark the original question did not ask what if I wanted to divide by text as well who does that not me!?

 

[inarbeth]

I shall now retire from this thread having been p***ed on from a great height.

 

[Iain Lewis]

Easy guys - I though the idea of these open forum type message boards was to pass on some of our experience / knowledge to other Excel users.

Personally I can see benefits from both formulas. I guess it's up to the person who who asked for help to decide which is most suitable for their needs. And if nothing else, someone else reading the messages may be able to use some of the alternates offered.

Don't sweat the small stuff. Enjoy the rest of your day :wink:

 

[Chris Davison]

On 2002-05-08 00:27, inarbeth wrote:
I shall now retire from this thread having been p***ed on from a great height.

Ian,

that's one way of looking at it !

another way, maybe, is that you've just been privvy to some excellent alternative one-to-one advice and help, at your own leisure : the sort of advice that would cost you £100 per hour if you did it privately

and he's around to stay, with his main incentive being sharing his expert knowledge, not furthering his own ego by pissing on people like us

he hasn't pissed on you from a great height, he's just nudged you and pointed you in the new direction of an open Excel door you may not have spotted earlier...

Chris

 

[billtester]

Good afternoon those that are more advanced than I! What is the solution to get the following formula to show 9% instead of 91%?

=IF(ISERROR(1-(F54/H54)),0,F54/H54)

Thank you in advance for your help!

 

[sous2817]

One solution would be to put a 9 in F54 and a 100 in H54.

I'm sure there are others, but if you could provide a bit more information and a bit more context, you'll probably get a better answer.