NEED TO GET RID OF "N/A" FROM FORMULA RESULT.

[garyt153]

I have several other formulas that rely on the result of this formula. =IF(SUM(AC49-AD49=0),"",(SUM(AC49-AD49)))

Everything works fine, except when the cell "AC49" is N/A - then this formula returns #VALUE and then subsequent formulas dont work.

Is there a way to modify the above formula so that when cell AC49 shows N/A - this formula can return a blank cell - not #VALUE and nor N/A.

Many thanks

 

[Cubist]

Try with OR and ISERROR. Also, the SUM functions aren't necessary.

=IF(OR(AC49-AD49=0,ISERROR(AC49-AD49)),"",AC49-AD49)

 

[RobOrBob]

You could also test AC49:

=IF(OR(AC49-AD49=0,NOT(ISNUMBER(AC49))),"",AC49-AD49)

 

[garyt153]

=IF(OR(AC49-AD49=0,ISERROR(AC49-AD49)),"",AC49-AD49)

Thank you but still returns #VALUE!

 

[garyt153]

You could also test AC49:

=IF(OR(AC49-AD49=0,NOT(ISNUMBER(AC49))),"",AC49-AD49)

Thank you but still returns #VALUE!

 

[Cubist]

Thank you but still returns #VALUE!

What does it return when you do?

=AC49-AD49

 

[RobOrBob]

The OR is causing the problem so:
=IF(NOT(ISNUMBER(AC49)),"",IF(AC49-AD49=0,"",AC49-AD49))

 

[garyt153]

What does it return when you do?

=AC49-AD49

#VALUE!

 

[garyt153]

The OR is causing the problem so:
=IF(NOT(ISNUMBER(AC49)),"",IF(AC49-AD49=0,"",AC49-AD49))

Thank you - that seems to work. I will try it on some other sheets to make sure but looking good.

 

[Cubist]

#VALUE!

It suggests that one or both values in AC49 and AD49 are not real numbers. The solution Rob suggested will sometimes yield unwanted result if the mentioned issue isn't addressed.