Escel Help

[Andy100]

Hi guys,

Is there any way to make an invalid formula row return a blank or zero rather than n/a ?

I'm trying to use the results of a formula in another calculation but the n\a's are causing me grief.



Gots to love my spelling of Excel in the title :s

 

[Namsnik]

=if(Isna(A1),"",YourFormula)

It would help if you shared the formula you are trying to adapt.
In some circumstances this will do it:
=if(Isna(A1),"",YourFormula)

However this won't help if your formula is =sum(A1:A40). In that case it is better to deal with the error in the original data though there may be a solution.

Have a look at the Information Category in the Function Wizard for how to test for other error conditions as well. From Excel help:
Information functions
CELL Returns information about the formatting, location, or contents of a cell
ERROR.TYPE Returns a number corresponding to an error type
INFO Returns information about the current operating environment
ISBLANK Returns TRUE if the value is blank
ISERR Returns TRUE if the value is any error value except #N/A
ISERROR Returns TRUE if the value is any error value
ISEVEN Returns TRUE if the number is even
ISLOGICAL Returns TRUE if the value is a logical value
ISNA Returns TRUE if the value is the #N/A error value
ISNONTEXT Returns TRUE if the value is not text
ISNUMBER Returns TRUE if the value is a number
ISODD Returns TRUE if the number is odd
ISREF Returns TRUE if the value is a reference
ISTEXT Returns TRUE if the value is text
N Returns a value converted to a number
NA Returns the error value #N/A
TYPE Returns a number indicating the data type of a value

 

[A[L]C]

Also depends which version of Excel you are using