Avoiding writing complex expressions twice

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)


"xirx" <xirx@gmx.de> wrote in message
news:423a069f$0$4362$4d4ebb8e@read.news.de.uu.net...
>
> 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)
>
>
>

Bob Phillips wrote:

> Put the complex expression in another cell, and reference that.
>
> =IF(ISNA(A1);0;A1)
>

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...

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

--

HTH

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


"xirx" <xirx@gmx.de> wrote in message
news:423a0e79$0$4263$4d4ebb8e@read.news.de.uu.net...
> Bob Phillips wrote:
>
> > Put the complex expression in another cell, and reference that.
> >
> > =IF(ISNA(A1);0;A1)
> >
>
> 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).



xirx wrote:
>
> 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)

--

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.



xirx wrote:
>
> 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)

--

Dave Peterson

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"xirx" <xirx@gmx.de> wrote in message
news:423a069f$0$4362$4d4ebb8e@read.news.de.uu.net...
>
> 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)
>
>
>

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
"xirx" <xirx@gmx.de> wrote in message
news:423a069f$0$4362$4d4ebb8e@read.news.de.uu.net...
>
> 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)
>
>
>