How do I avoid repeating calculation in IF function?

[Arthur]

I use a calculation that sometimes (legitimately) returns the error code
#N/A. When this happens, I want a blank stored in the cell. So:
IF(ISNA(calculation),"",calculation)
Is there some way of avoiding the second calculation?

 

[Shane Devenshire]

Depends on your version of Excel, and what the calculation you are making is.

2007:

=IFERROR(Calculation,"")

for 2003 you will need to tell us the calculation. If it is VLOOKUP then
the answer is probably no.

 

[Shane Devenshire]

Hi,

Depends on your version of Excel, and what the calculation you are making is.

2007:

=IFERROR(Calculation,"")

for 2003 you will need to tell us the calculation. If it is VLOOKUP then
the answer is probably no.

 

[Bernard Liengme]

If this is just for cosmetic purposes, you could leave out the IF and use
conditional formatting to hide the #N/A when it occurs.
best wishes

 

[T. Valko]

It all depends on what your formula looks like.

Sometimes, instead of repeating the formula like this:

=IF(ISNA(formula),"",formula)

You can reduce the error trap to the specific portion of the formula that
actually generates the error. For example:

=IF(ISNA(VLOOKUP(A1,X:Y,2,0)),"",VLOOKUP(A1,X:Y,2,0))

In this case we know an error will be generated if the lookup value A1
doesn't exist in the lookup table column X. While we can't completely
eliminate** a double formula we can make it more efficient and save a few
keystrokes at the same time:

=IF(COUNTIF(X:X,A1),VLOOKUP(A1,X:Y,2,0),"")

** Excel 2007 comes with a new error testing function called IFERROR. Using
that function combined with the above VLOOKUP formula:

=IFERROR(VLOOKUP(A1,X:Y,2,0),"")

In this case we did eliminate the double formula. However, this is not
always the most efficient method to use on really long complex formulas.

 

[Stephen Bye]

It all depends on what your formula looks like.

Sometimes, instead of repeating the formula like this:

=IF(ISNA(formula),"",formula)

You can reduce the error trap to the specific portion of the formula that
actually generates the error.

There is an easier way.
Put the formula in a different cell, then test the result of that.
For example, instead of putting
=IF(ISNA(VLOOKUP(A1,X:Y,2,0)),"",VLOOKUP(A1,X:Y,2,0))
in cell B2, put
=VLOOKUP(A1,X:Y,2,0)
in B1, and
=IF(ISNA(B1),"",B1)
in B2.