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

64
votes

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
4
excelexcel-formula
are you asking for an excel formula or VBA code? – JustAnotherCoder
sorry if i wasnt clear, im asking for an excel formula :) – bobbyjones
use IFNA Function for Excel 2016, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Mobile, Excel for Android phones (support.office.com/en-us/article/…) – Fritz

4 Answers

113
votes

Input the following formula in C1:

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

Screenshots:

When #N/A:

enter image description here

When not #N/A:

enter image description here

Let us know if this helps.

6
votes

You can also use IFNA(expression, value)

1
votes

"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

0
votes

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]