Value Error

N

nastech

not sure how to fix..
basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items
cannot be equal; note: i get if both equal, then nothing, but longer
equation results, below needed for other calculation.. just need to fix 1st
item "trying" below).
thanks...

trying: (finding some numbers are only different by .0001; argh)
=CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error

trying to embed in some fasion, to keep space down, critical. else:

=IF(CR383-CQ383=0,
((CS383-CQ383)/((CR383-0.0001)-CQ383)*10),
((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem

trying to reduce to:
=((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10)
 
B

Bernard Liengme

Let's try to simplify. You have two numbers; let them be in A1 and B1 for
simplicity
Now tell use what you want to compute.
best wishes
 
N

nastech

hi, thanks for reply, know I may not know how to ask sometimes, but usually
certain basic math not at issue, but commands such as IF, VALUE.. for
"syntax".

major concern was just subtracting 2 numbers, that would be used in a
division problem: did not want zero result. found answer, but curious if
there is a simpler way.

answer was in syntax, just needed to replace the ditto's, with a zero to get
rid of value error, subsequent division works. thanks.

=((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10)
=((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,0))-CQ383)*10)
 
P

pinmaster

Hi,
=CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error
Click to expand...

The error is the result of the double quotes "", replace with 0.

=CR383-IF(CR383-CQ383=0,0.0001,0)

as for the second part of your problem, you could use an error trap like
"if(iserror(formula),"",formula)" but that would lenghten the formula not
shorten it, one solution might be to make it invisible using conditinonal
formatting with: =ISERROR(A1) and formatting the text white.


HTH
Jean-Guy
 
B

Bernard Liengme

Hello Nastech,
As I read this, basically you want (CS383-CQ383)*10/(CR383-CQ383) but
when the denominator ends up as zero (ie CR383=CQ383) you want
(CS383-CQ383)*10/(0.0001)

Your formula has extra parentheses
=((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,0))-CQ383)*10)
this is the same:
=(CS383-CQ383)/(CR383-IF(CR383-CQ383=0,0.0001,0)-CQ383)*10
and is easier to read, I think.
But a reader may misinterpret the 10, so I would use
=(CS383-CQ383)*10/(CR383-IF(CR383-CQ383=0,0.0001,0)-CQ383)

Here is a way without IF
=(CS383-CQ383)*10/((CR383-CQ383-(CR383=CQ383)*0.0001))

The term -(CR383=CQ383)*0.0001 is evaluated thus:
The term (CR383=CQ383) this is either TRUE or FALSE;
but when we perform an arithmetic operation (add, subtract, multiply or
divide) on a Boolean value (T or F), Excel treats TRUE as 1 and FALSE as 0
So, if the two values (CR383 and CQ383) are equal then the denominator
becomes
(0 - 1*0.0001) or 0.0001

By the way: the symbol " is best referred to a quote mark (or double quote).
True, it is used as a ditto abbreviation in lists but Excel Help and
English-speaking Excel users will call it a quote mark or quotation mark.

best wishes
 
Top