CODEWITHSUNDEEP
excelexcel-formula
bobbyjones
bobbyjones

Reputation: 2079

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

Upvotes: 72

Views: 408007

Answers (6)

Steven Lau
Steven Lau

Reputation: 1

You can also use IFERROR function. This function let's you return a desired value with the formula results in an #N/A error.

In your example try: =IFERROR(A1*B1,B1)

So if A1 is a value then it will calculate "A1*B1". If A1 is #N/A this calculation will error trying to multiply, so the formula will just result in "B1".

Upvotes: 0

cpbr
cpbr

Reputation: 11

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

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

Upvotes: 1

Micah Chaney
Micah Chaney

Reputation: 69

You can also use IFNA(expression, value)

Upvotes: 5

barry houdini
barry houdini

Reputation: 46341

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]

Upvotes: 0

WGS
WGS

Reputation: 14169

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.

Upvotes: 130

bto.rdz
bto.rdz

Reputation: 6720

"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

Upvotes: 2

Related Questions