How do I avoid repeating calculation in IF function?

  • Thread starter Thread starter Arthur
  • Start date Start date
A

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?
 
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.
 
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.
 
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
 
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.
 
T. Valko said:
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.
Click to expand...

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