Convert #N/A to 0

G

Guest

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.
 
H

HansM

You would want to modify your formula to make use of the ISNA() formula.
Something like:
=IF(ISNA(YourFormulaHere),0,YourFormulaHere)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Chart plotting/getting n/a value 4
How to deal with "#N/A"??? 2
Replacing #N/A 1
Convert #N/A to number 2
#N/A - Excel 2
Formulae when some cells contain #N/A 6
Equation setup problem 2
equaton with #'s Y and N 1

Top