Writing an IF() formula to use an alternate cell if the primary cell doesn't have a value
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?
microsoft-excel worksheet-function microsoft-excel-2010
edited Dec 14 at 2:51
robinCTS
3,99941527
asked Dec 13 at 14:26
Corey
1
add a comment |
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?
microsoft-excel worksheet-function microsoft-excel-2010
edited Dec 14 at 2:51
robinCTS
3,99941527
asked Dec 13 at 14:26
Corey
1
add a comment |
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?
microsoft-excel worksheet-function microsoft-excel-2010
edited Dec 14 at 2:51
robinCTS
3,99941527
asked Dec 13 at 14:26
Corey
1
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?
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
edited Dec 14 at 2:51
robinCTS
3,99941527
asked Dec 13 at 14:26
Corey
1
edited Dec 14 at 2:51
robinCTS
3,99941527
asked Dec 13 at 14:26
Corey
1
edited Dec 14 at 2:51
robinCTS
3,99941527
edited Dec 14 at 2:51
robinCTS
3,99941527
edited Dec 14 at 2:51
robinCTS
3,99941527
3,99941527
asked Dec 13 at 14:26
Corey
1
asked Dec 13 at 14:26
Corey
1
asked Dec 13 at 14:26
Corey
1
1
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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)
answered Dec 13 at 23:36
fixer1234
17.7k144581
add a comment |
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.
edited Dec 14 at 2:51
robinCTS
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383311%2fwriting-an-if-formula-to-use-an-alternate-cell-if-the-primary-cell-doesnt-hav%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Dec 13 at 23:36
fixer1234
17.7k144581
add a comment |
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)
answered Dec 13 at 23:36
fixer1234
17.7k144581
add a comment |
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)
answered Dec 13 at 23:36
fixer1234
17.7k144581
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)
answered Dec 13 at 23:36
fixer1234
17.7k144581
edited Dec 14 at 0:31
answered Dec 13 at 23:36
fixer1234
17.7k144581
answered Dec 13 at 23:36
fixer1234
17.7k144581
answered Dec 13 at 23:36
fixer1234
17.7k144581
17.7k144581
add a comment |
add a comment |
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.
edited Dec 14 at 2:51
robinCTS
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
add a comment |
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.
edited Dec 14 at 2:51
robinCTS
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
add a comment |
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.
edited Dec 14 at 2:51
robinCTS
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
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.
edited Dec 14 at 2:51
robinCTS
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
edited Dec 14 at 2:51
robinCTS
3,99941527
edited Dec 14 at 2:51
robinCTS
3,99941527
edited Dec 14 at 2:51
robinCTS
3,99941527
3,99941527
answered Dec 13 at 14:30
Scott Craner
11.2k1815
answered Dec 13 at 14:30
Scott Craner
11.2k1815
answered Dec 13 at 14:30
Scott Craner
11.2k1815
11.2k1815
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383311%2fwriting-an-if-formula-to-use-an-alternate-cell-if-the-primary-cell-doesnt-hav%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
