Or function with invalid number "zero/zero"
 

I have created a formula IF(OR(B1=0,A1=0,B1/A1=99.5%),0,B1/A1). If the
value of B1=0, the formula seems to ignore the first 2 criteria and focuses
on the 3rd logical portion. I don't understand this since the OR function
should return "TRUE" if any of the conditions are met, which in this case is
met on the 1st one. Why is this? Note if you remove the 3rd logical
scenario of B1/A1 the formula works, so I know without a doubt this is where
the problem is, I just can't figure out why or how to get around it.
--
Thank you

Or function with invalid number "zero/zero"
 

It evaluates every value within the OR function before returning a value, so
if A1=0 B1/A1 will return #DIV/0! Try doing it this way:

=IF(OR(B1=0,A1=0,),0,IF(B1/A1=99.5%,0,B1/A1))
--
Regards,
Dave


"Mandy" wrote:

I have created a formula IF(OR(B1=0,A1=0,B1/A1=99.5%),0,B1/A1). If the
value of B1=0, the formula seems to ignore the first 2 criteria and focuses
on the 3rd logical portion. I don't understand this since the OR function
should return "TRUE" if any of the conditions are met, which in this case is
met on the 1st one. Why is this? Note if you remove the 3rd logical
scenario of B1/A1 the formula works, so I know without a doubt this is where
the problem is, I just can't figure out why or how to get around it.
--
Thank you

Or function with invalid number "zero/zero"
 

On Wed, 9 Aug 2006 07:51:01 -0700, Mandy
wrote:

:-)= I have created a formula IF(OR(B1=0,A1=0,B1/A1=99.5%),0,B1/A1). If the
:-)= value of B1=0, the formula seems to ignore the first 2 criteria and focuses
:-)= on the 3rd logical portion. I don't understand this since the OR function
:-)= should return "TRUE" if any of the conditions are met, which in this case is
:-)= met on the 1st one. Why is this? Note if you remove the 3rd logical
:-)= scenario of B1/A1 the formula works, so I know without a doubt this is where
:-)= the problem is, I just can't figure out why or how to get around it.

Dealing with percenyages I frequently run into divide by zero.
Every set of blank cells awaiting future event input yields a divide
by zero in the percentage column.

I use:

A1/IF(B1=0,0.000001, B1)
or
SUM(A1:A12)/IF(SUM(B1:B12)=0,0.000001, SUM(B1:B12))

My percentages usually have 2 decimal places, sometimes 1, rarely 3 or
more.
..67375 =67.375 % and my percentages rarely require 1/100,000 accuracy.

Therefore every blank cell yields zero per cent because it divides
zero by 0.000001, a very small number, instead of zero.
If more decimal places are required, divide by a number smaller than
0.000001 like 0.000000001, or whtever is necessary.

HTH

Dante

--
Posted via a free Usenet account from http://www.teranews.com