if or problem

B

bastien86

I have the data

0.102365034 9.419
#NUM! #NUM!
0 div/0

I put the formula in:

=IF(OR(A1=0,ISERROR(A1)),b0,1/a1-1/2.86)

for the column on the right and filled down. How come my 2nd and 3rd lines
on the right column aren't 9.419
 
T

Toppers

Assuming first column is data and second expected result:

for data of #NUM! will fail (error) on the "A1=0" (non-numeric) test while
for A1=0, your test returns zero (not div/0). Your test will only work for A1
being a non-zero valid number. (I assume b0 is a typo).

Why should they be 9.419?
 
T

Tim M

In your formula you are telling it not to do the calculation if it is a 0 or
and error. The question is what is b0? Did you mean for that to be a cell,
perhaps B1?
 
B

bastien86

its b0 because the i knew that data wouldn't fail and put it at the top, the
data after that would then see if it was either a fail or a 0 in the a
column, and if it was return the value above it or do the calculation.
 
B

bastien86

b0 was put in simply to say, if the a column is not 0 or an error then place
the value above the current cell in the cell containing the formula.
 
B

bastien86

The formula works fine for

=if(iserror(a1),b0,1/a1-1/2.5)

but then i have 0 in my a column and it screws up the false part cause 1/0
gives div/0

i want to say iserror or a1=0 but i get problems:\
 
B

bastien86

i figured it out, the #num was screwing up for the test if a2=0, i had to do
=if(iserror(a1),b0,if(a1=0,b0,1/a1-1/2.5))
 
Top