Avoiding writing complex expressions twice

X

xirx

Is there a way to avoid the use of some-complex-expression in
thix formula?

if(isna(some-complex-expression); 0; some-complex-expression)

In some programming languages, 0 means false and any other
value means true and the OR(X,Y) actually means: if(X;X;Y).
Thus, you can use the OR as a shortcut for this if-statement
and avoid notation and evaluation of X, twice.

But Exel's OR returns TRUE or FALSE...

So: Is there any way to shorten

if(isna(some-complex-expression); 0; some-complex-expression)
 
B

Bob Phillips

Put the complex expression in another cell, and reference that.

=IF(ISNA(A1);0;A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
X

xirx

Bob said:
Put the complex expression in another cell, and reference that.

=IF(ISNA(A1);0;A1)
Click to expand...

Well, that's the obvious work-around... thank you very much
for your answer. But this is not really the answer I am
looking for...
 
D

Dave Peterson

You might be able to reduce the complex expression to just the part that's
causing the error, but, in general, that's the way xl works.

If it's just a matter of formatting, you could use format|conditional formatting
to hide that error (white font on white background).
 
D

Dave Peterson

Another option may be to create a UDF that evaluates the expression and returns
the default or the result of the expression.

Kind of like:

Option Explicit
Function ISNADefault(myExpression As String, myDefault As Variant) As Variant

Application.Volatile

Dim res As Variant
res = Application.Evaluate(myExpression)

If IsError(res) Then
ISNADefault = myDefault
Else
ISNADefault = res
End If

End Function

then in the worksheet:
=isnadefault("vlookup(a1,sheet2!a1:b99,2,false)",0)

But I wouldn't use this. Since you're using strings as the formula, it doesn't
copy very nicely. And doesn't recalculate if you change A1--the function
doesn't know that it depends on A1 (or sheet2!a1:b99).

And I bet it would be far slower than the "double" call of the function in the
cell. Using the helper cell sounds like the quickest method.
 
S

Sandy Mann

It may depend on the complexity of the expression but would making the
expression a named formula help? Then you can put:

If (isna(NamedFormula),0,NamedFormula)

Regards

Sandy
 
B

Bob Phillips

Then how abut creating the repetitive part as a workbook name?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dr. Vladimir Mindin

Simple way is to separate (some-complex-expression) and if statement:
column C.......................................... column D
(some-complex-expression)........... if(isna(c1),0,c1)
(some-complex-expression)........... if(isna(c2),0,c2)
........
........
May be it looks not to smart, but it is foolproof, and easy to maintain

Regards,
Vladimir Mindin
 
Top