Using "If cell contains #N/A" as a formula condition.

I need help on my Excel sheet. How can I declare the following IF condition properly?

if A1 = "n/a" then C1 = B1 else if A1 != "n/a" or has value(int) then C1 = A1*B1 

5 Answers

Input the following formula in C1:

=IF(ISNA(A1),B1,A1*B1)

Screenshots:

When #N/A:

When not #N/A:

Let us know if this helps.

You can also use IFNA(expression, value)

"N/A" is not a string it is an error, try this:

=if(ISNA(A1),C1) 

you have to place this fomula in cell B1 so it will get the value of your formula

A possible alternative approach in Excel 2010 or later versions:

AGGREGATE(6,6,A1,B1)

In AGGREGATE function the first 6 indicates PRODUCT operation and the second 6 denotes "ignore errors"

[untested]

use this formula to return the results for case of "FALSE"/"TRUE":

=IF(ISNA(A1)=TRUE, B1, A1*B1)