0

I'm trying to write a formula to subtract two cells but only if they have values. If there isn't a value in one of them I want to use a different cell in the subtraction.

For example:

   |   A  |   B  |
---+------+------+
 1 | 1200 |  N/A |
 2 |  400 |  400 |
 3 |  100 |  600 |

I want to subtract A1 from A3 and so on along the worksheet. However, in column B there is an N/A so I would like to subtract B2 from B3 instead.

How would I write that If/Then statement?

2

I'll add this just for completeness. The question is a little ambiguous as to whether the "N/A" is an intentional text value or the #N/A error condition, so I'll address both.

As asked in the question, you can use an IF test in different ways to check the cell content. If that target value is the text string "N/A", you can test for that with an equality (A1="N/A"). If that target value is actually a #N/A error, or you just want to verify that the cell contains a number, there are even dedicated test functions, like ISNA and ISNUMBER. These will give you the True/False result for the IF condition without using an equality to test the cell value against another value. That approach would look generally like this:

IF(ISNA(A1),A3-A2,A3-A1)

or

IF(ISNUMBER(A1),A3-A1,A3-A2)

Scott Craner's answer is often a better approach. IFERROR is a special dedicated IF test with two benefits. One is that it will handle any kind of error condition, not just #N/A. For example, if either cell contains any error condition, or text (including "N/A") that you try to use in arithmetic, IFERROR will handle the result.

The second benefit is that it is a "short form" of an IF test. It doesn't require the normal three arguments (test condition, true result, false result). It needs only the "normal" formula, and the alternate formula or result to use if the normal one produces an error. This often saves testing a formula, and then having to repeat the formula in order to use it. The formula, itself, serves as the test condition.

However, I can think of one situation where IFERROR might not be desirable. Say your data contains the text string "N/A" as an intentional placeholder for missing data. You also want to use only that placeholder to determine which formula to use. Further, if there is any other non-numeric cell content, or any error condition, it's important for you to know it. In that case, you would want to use an IF test that checks specifically for "N/A". That would look like this:

IF(A1="N/A",A3-A2,A3-A1)
1

I would use IFERROR:

=IFERROR(A3-A1,A3-A2)

And copy accross. If the Value in A1 is an error or text it will error and the next function will resolve.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.