how to subtract previous cell containing number in excel
I have an Excel table like this and want to get RESULT(subtracting row numbers of cells which contain 1). I have thousands of rows so I need a formula.
ROW NMB Value RESULT
1 0
2 1 2-1=1
3 0
4 0
5 0
6 1 6-2=4
7 0
8 0
9 1 9-6=3
microsoft-excel
edited Jan 14 at 19:43
Worthwelle
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
add a comment |
I have an Excel table like this and want to get RESULT(subtracting row numbers of cells which contain 1). I have thousands of rows so I need a formula.
ROW NMB Value RESULT
1 0
2 1 2-1=1
3 0
4 0
5 0
6 1 6-2=4
7 0
8 0
9 1 9-6=3
microsoft-excel
edited Jan 14 at 19:43
Worthwelle
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
Do you mean=IF(B2>0,A2-B2,"")?
– Máté Juhász
Jan 14 at 18:19
I think it might be=IF(C4>0,A4-SUM(D$1:D3),"")you are looking for.
– cybernetic.nomad
Jan 14 at 18:39
1
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53
add a comment |
I have an Excel table like this and want to get RESULT(subtracting row numbers of cells which contain 1). I have thousands of rows so I need a formula.
ROW NMB Value RESULT
1 0
2 1 2-1=1
3 0
4 0
5 0
6 1 6-2=4
7 0
8 0
9 1 9-6=3
microsoft-excel
edited Jan 14 at 19:43
Worthwelle
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
I have an Excel table like this and want to get RESULT(subtracting row numbers of cells which contain 1). I have thousands of rows so I need a formula.
ROW NMB Value RESULT
1 0
2 1 2-1=1
3 0
4 0
5 0
6 1 6-2=4
7 0
8 0
9 1 9-6=3
microsoft-excel
microsoft-excel
edited Jan 14 at 19:43
Worthwelle
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
edited Jan 14 at 19:43
Worthwelle
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
edited Jan 14 at 19:43
Worthwelle
2,68431325
edited Jan 14 at 19:43
Worthwelle
2,68431325
edited Jan 14 at 19:43
Worthwelle
2,68431325
2,68431325
asked Jan 14 at 18:11
zzz1511zzz1511
41
asked Jan 14 at 18:11
zzz1511zzz1511
41
asked Jan 14 at 18:11
zzz1511zzz1511
41
41
Do you mean=IF(B2>0,A2-B2,"")?
– Máté Juhász
Jan 14 at 18:19
I think it might be=IF(C4>0,A4-SUM(D$1:D3),"")you are looking for.
– cybernetic.nomad
Jan 14 at 18:39
1
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53
add a comment |
Do you mean=IF(B2>0,A2-B2,"")?
– Máté Juhász
Jan 14 at 18:19
I think it might be=IF(C4>0,A4-SUM(D$1:D3),"")you are looking for.
– cybernetic.nomad
Jan 14 at 18:39
1
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53
Do you mean
=IF(B2>0,A2-B2,"")?– Máté Juhász
Jan 14 at 18:19
Do you mean
=IF(B2>0,A2-B2,"")?– Máté Juhász
Jan 14 at 18:19
I think it might be
=IF(C4>0,A4-SUM(D$1:D3),"") you are looking for.– cybernetic.nomad
Jan 14 at 18:39
I think it might be
=IF(C4>0,A4-SUM(D$1:D3),"") you are looking for.– cybernetic.nomad
Jan 14 at 18:39
1
1
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53
add a comment |
1 Answer
1
active
oldest
votes
By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.
put :
B1 ----> =IF(A1="","",IF(A1=1,ROW(),0))
C1 ----> =IF(A1=1,B1,"")
then :
B2 ----> =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2 ----> =IF(A2=1,B2-B1,"")
and drag downwards.
Done. You can hide of move column B elsewhere if needed.
Hope it helps. ( :
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
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%2f1394216%2fhow-to-subtract-previous-cell-containing-number-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.
put :
B1 ----> =IF(A1="","",IF(A1=1,ROW(),0))
C1 ----> =IF(A1=1,B1,"")
then :
B2 ----> =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2 ----> =IF(A2=1,B2-B1,"")
and drag downwards.
Done. You can hide of move column B elsewhere if needed.
Hope it helps. ( :
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
add a comment |
By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.
put :
B1 ----> =IF(A1="","",IF(A1=1,ROW(),0))
C1 ----> =IF(A1=1,B1,"")
then :
B2 ----> =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2 ----> =IF(A2=1,B2-B1,"")
and drag downwards.
Done. You can hide of move column B elsewhere if needed.
Hope it helps. ( :
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
add a comment |
By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.
put :
B1 ----> =IF(A1="","",IF(A1=1,ROW(),0))
C1 ----> =IF(A1=1,B1,"")
then :
B2 ----> =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2 ----> =IF(A2=1,B2-B1,"")
and drag downwards.
Done. You can hide of move column B elsewhere if needed.
Hope it helps. ( :
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.
put :
B1 ----> =IF(A1="","",IF(A1=1,ROW(),0))
C1 ----> =IF(A1=1,B1,"")
then :
B2 ----> =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2 ----> =IF(A2=1,B2-B1,"")
and drag downwards.
Done. You can hide of move column B elsewhere if needed.
Hope it helps. ( :
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
answered Jan 17 at 3:43
p._phidot_p._phidot_
633312
633312
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.
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%2f1394216%2fhow-to-subtract-previous-cell-containing-number-in-excel%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
Do you mean
=IF(B2>0,A2-B2,"")?– Máté Juhász
Jan 14 at 18:19
I think it might be
=IF(C4>0,A4-SUM(D$1:D3),"")you are looking for.– cybernetic.nomad
Jan 14 at 18:39
1
Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it?
– fixer1234
Jan 14 at 21:52
I think the RESULT for the 1st '1' value is '2-0=2'.. right?
– p._phidot_
Jan 15 at 3:53