Convert #N/A to 0

[Sheila P]

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.

[Peo Sjoblom]

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

--

Regards,

Peo Sjoblom

"Sheila P" <Sheila P@discussions.microsoft.com> wrote in message
news:5977A7A0-4040-4104-BAFD-A9E0A5A7E3F8@microsoft.com...
> 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.
>

[HansM]

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 P@discussions.microsoft.com> wrote in message
news:5977A7A0-4040-4104-BAFD-A9E0A5A7E3F8@microsoft.com...
> 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.
>