Convert #N/A to 0
 

Hi,

I have a formula that returns #N/A in some cells. I want to be able to
convert the #N/A cells to the value 0. I have tried using the ERROR.TYPE
formula in an IF function and it converts #N/A cells to 0, however, it also
converts the non-#N/A cells to #N/A. I need to keep the value in the
non-#N/A cells. According to the help file for this function, I should be
able to have the value in the cell returned if it is a false statement. Ex.
IF(ERROR.TYPE(E8)=7),0,E8) If E8 = 100, this formula should return 100 and
not #N/A for that cell.

Any assistance would be greatly appreciated.

=IF(ISNA(formula),0,formula)

--

Regards,

Peo Sjoblom

"Sheila P" <Sheila wrote in message
...
Hi,

I have a formula that returns #N/A in some cells. I want to be able to
convert the #N/A cells to the value 0. I have tried using the ERROR.TYPE
formula in an IF function and it converts #N/A cells to 0, however, it
also
converts the non-#N/A cells to #N/A. I need to keep the value in the
non-#N/A cells. According to the help file for this function, I should be
able to have the value in the cell returned if it is a false statement.
Ex.
IF(ERROR.TYPE(E8)=7),0,E8) If E8 = 100, this formula should return 100
and
not #N/A for that cell.

Any assistance would be greatly appreciated.

You would want to modify your formula to make use of the ISNA() formula.
Something like:
=IF(ISNA(YourFormulaHere),0,YourFormulaHere)
--
HansM
"Sheila P" <Sheila wrote in message
...
Hi,

I have a formula that returns #N/A in some cells. I want to be able to
convert the #N/A cells to the value 0. I have tried using the ERROR.TYPE
formula in an IF function and it converts #N/A cells to 0, however, it
also
converts the non-#N/A cells to #N/A. I need to keep the value in the
non-#N/A cells. According to the help file for this function, I should be
able to have the value in the cell returned if it is a false statement.
Ex.
IF(ERROR.TYPE(E8)=7),0,E8) If E8 = 100, this formula should return 100
and
not #N/A for that cell.

Any assistance would be greatly appreciated.