Division By Zero

In this formula: = b24/b5

If b5 is blank or zero, can the formula be written so that it will return
"NoOrders" ?

Thank you in advance.

=IF(N(B5),B24/B5,"NoOrders")

carl wrote:
> In this formula: = b24/b5
>
> If b5 is blank or zero, can the formula be written so that it will return
> "NoOrders" ?
>
> Thank you in advance.

Hi, Aladin:

I was going to say that doesn't work for me, but surprisingly (to me), it
does!

According to the documentation for the N function, it shouldn't work. If I
type a 0 in B5, then write in another cell the formula =N(B5), I get 0. But
your formula returns NoOrders.

The question is, WHY? Let's say you have 100 in B24 and 0 in B5. N tests
whether 0 is a number, which it is. Therefore the formula *should* return
100/0, and ultimately a divide-by-zero error.

OTOH, if you change the formula to

=IF(N(B24/B5),B24/B5,"NoOrders")

you get #DIV/0! rather than NoOrders. B24/B5 in this case returns an error
value, and N(B24/B5) returns that same error value, #DIV/0! IF seems to treat
this error result as non-0, and returns the result of B24/B5, or an error.

I find this behavior to be very bizarre and counter-intuitive.

I would use a formula that doesn't produce all of these "surprises", maybe
something like

=IF(ISERROR(B24/B5),"NoOrders",B24/B5)



On Mon, 26 Sep 2005 19:41:11 +0200, Aladin Akyurek <akyurek@xs4all.nl> wrote:

>=IF(N(B5),B24/B5,"NoOrders")
>
>carl wrote:
>> In this formula: = b24/b5
>>
>> If b5 is blank or zero, can the formula be written so that it will return
>> "NoOrders" ?
>>
>> Thank you in advance.

Myrna,

If the condition part in a IF formula evaluates to 0, which means FALSE
while a non-zero numeric result is taken as TRUE, and N(0)=0, IF will
proceed to its then-part.

Aladin

Myrna Larson wrote:
> Hi, Aladin:
>
> I was going to say that doesn't work for me, but surprisingly (to me), it
> does!
>
> According to the documentation for the N function, it shouldn't work. If I
> type a 0 in B5, then write in another cell the formula =N(B5), I get 0. But
> your formula returns NoOrders.
>
> The question is, WHY? Let's say you have 100 in B24 and 0 in B5. N tests
> whether 0 is a number, which it is. Therefore the formula *should* return
> 100/0, and ultimately a divide-by-zero error.
>
> OTOH, if you change the formula to
>
> =IF(N(B24/B5),B24/B5,"NoOrders")
>
> you get #DIV/0! rather than NoOrders. B24/B5 in this case returns an error
> value, and N(B24/B5) returns that same error value, #DIV/0! IF seems to treat
> this error result as non-0, and returns the result of B24/B5, or an error.
>
> I find this behavior to be very bizarre and counter-intuitive.
>
> I would use a formula that doesn't produce all of these "surprises", maybe
> something like
>
> =IF(ISERROR(B24/B5),"NoOrders",B24/B5)
>
>
>
> On Mon, 26 Sep 2005 19:41:11 +0200, Aladin Akyurek <akyurek@xs4all.nl> wrote:
>
>
>>=IF(N(B5),B24/B5,"NoOrders")
>>
>>carl wrote:
>>
>>>In this formula: = b24/b5
>>>
>>>If b5 is blank or zero, can the formula be written so that it will return
>>>"NoOrders" ?
>>>
>>>Thank you in advance.

Myrna,

I'm not, (can't), trying to answer for Aladin, but I don't understand what
you mean by:

> According to the documentation for the N function, it shouldn't work

My Help says for N:

*************************
If value is or refers to N returns
A number That number
************************

If N returns a 0 then it is the same as:

=IF(0,"Alex","Sandy")

which will return "Sandy"

Why do you find it counter-intuitive?

--
Puzzled,


Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:mufgj1pkdvuqjb60iet22m1ukotfcc536h@4ax.com...
> Hi, Aladin:
>
> I was going to say that doesn't work for me, but surprisingly (to me), it
> does!
>
> According to the documentation for the N function, it shouldn't work. If I
> type a 0 in B5, then write in another cell the formula =N(B5), I get 0.
> But
> your formula returns NoOrders.
>
> The question is, WHY? Let's say you have 100 in B24 and 0 in B5. N tests
> whether 0 is a number, which it is. Therefore the formula *should* return
> 100/0, and ultimately a divide-by-zero error.
>
> OTOH, if you change the formula to
>
> =IF(N(B24/B5),B24/B5,"NoOrders")
>
> you get #DIV/0! rather than NoOrders. B24/B5 in this case returns an error
> value, and N(B24/B5) returns that same error value, #DIV/0! IF seems to
> treat
> this error result as non-0, and returns the result of B24/B5, or an error.
>
> I find this behavior to be very bizarre and counter-intuitive.
>
> I would use a formula that doesn't produce all of these "surprises", maybe
> something like
>
> =IF(ISERROR(B24/B5),"NoOrders",B24/B5)
>
>
>
> On Mon, 26 Sep 2005 19:41:11 +0200, Aladin Akyurek <akyurek@xs4all.nl>
> wrote:
>
>>=IF(N(B5),B24/B5,"NoOrders")
>>
>>carl wrote:
>>> In this formula: = b24/b5
>>>
>>> If b5 is blank or zero, can the formula be written so that it will
>>> return
>>> "NoOrders" ?
>>>
>>> Thank you in advance.

Because my Alzheimer's is flaring up today???

On Mon, 26 Sep 2005 21:18:58 +0100, "Sandy Mann" <sandymann2@mailinator.com>
wrote:

>Myrna,
>
>I'm not, (can't), trying to answer for Aladin, but I don't understand what
>you mean by:
>
>> According to the documentation for the N function, it shouldn't work
>
>My Help says for N:
>
>*************************
>If value is or refers to N returns
>A number That number
>************************
>
>If N returns a 0 then it is the same as:
>
>=IF(0,"Alex","Sandy")
>
>which will return "Sandy"
>
>Why do you find it counter-intuitive?

Somehow I must have been thinking about ISNUMBER(B5), even though I was
reading help on the N() function. As I said, must be a "bad Alzheimer day".

On Mon, 26 Sep 2005 21:24:22 -0500, Myrna Larson
<anonymous@discussions.microsoft.com> wrote:

>Because my Alzheimer's is flaring up today???
>
>On Mon, 26 Sep 2005 21:18:58 +0100, "Sandy Mann" <sandymann2@mailinator.com>
>wrote:
>
>>Myrna,
>>
>>I'm not, (can't), trying to answer for Aladin, but I don't understand what
>>you mean by:
>>
>>> According to the documentation for the N function, it shouldn't work
>>
>>My Help says for N:
>>
>>*************************
>>If value is or refers to N returns
>>A number That number
>>************************
>>
>>If N returns a 0 then it is the same as:
>>
>>=IF(0,"Alex","Sandy")
>>
>>which will return "Sandy"
>>
>>Why do you find it counter-intuitive?