|
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 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 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" 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 wrote: Because my Alzheimer's is flaring up today??? On Mon, 26 Sep 2005 21:18:58 +0100, "Sandy Mann" 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? |
The doctor said to the patient, "I've got two pieces of bad news for you -
you've got cancer and Alzhimer's" "Oh well," said the patient, "At least I haven't got cancer!" <g -- Regards Sandy Replace@mailinator with @tiscali.co.uk "Myrna Larson" wrote in message ... 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 wrote: Because my Alzheimer's is flaring up today??? On Mon, 26 Sep 2005 21:18:58 +0100, "Sandy Mann" 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? |
| All times are GMT +1. The time now is 02:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com