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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Arthur" wrote:

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?

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 helps, please click the Yes button.

Cheers,
Shane Devenshire


"Arthur" wrote:

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?

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Arthur" wrote in message
...
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?

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.

--
Biff
Microsoft Excel MVP


"Arthur" wrote in message
...
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?

"T. Valko" wrote in message
...
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.