Avoiding writing complex expressions twice

  • Thread starter Thread starter xirx
  • Start date Start date
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)
 
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)
 
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...
 
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).
 
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.
 
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
 
Then how abut creating the repetitive part as a workbook name?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Back
Top