How to omit a row in calculations?
I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...)
A
1 10
2 10
3 15
4 27
When making a SUM(A:A) all figures in the column are taken into account.
Is there a way to temporarily disable a row? (say, row 3 above)
I could delete it but then the analysis gets complicated when some rows need to be taken into accounts, and some other not - different depending on the analysis.
I could create a column which would be a flag and build a formula around this - it seems awkward, though.
I am looking for something like PowerPoint Hide function: the slide exists but is not part of the presentation. Hiding in Excel does not work that: the row is not visible but still used in calculations.
microsoft-excel worksheet-function
asked Nov 22 at 13:59
WoJ
79731936
add a comment |
I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...)
A
1 10
2 10
3 15
4 27
When making a SUM(A:A) all figures in the column are taken into account.
Is there a way to temporarily disable a row? (say, row 3 above)
I could delete it but then the analysis gets complicated when some rows need to be taken into accounts, and some other not - different depending on the analysis.
I could create a column which would be a flag and build a formula around this - it seems awkward, though.
I am looking for something like PowerPoint Hide function: the slide exists but is not part of the presentation. Hiding in Excel does not work that: the row is not visible but still used in calculations.
microsoft-excel worksheet-function
asked Nov 22 at 13:59
WoJ
79731936
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
You could try shifting that row to the right, leaving a blank cell inA3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change15to=Z(15), whereZ()is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, makeZ()return its parameter. This allows you easily to include or exclude several cells at will.
– AFH
Nov 22 at 14:22
add a comment |
I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...)
A
1 10
2 10
3 15
4 27
When making a SUM(A:A) all figures in the column are taken into account.
Is there a way to temporarily disable a row? (say, row 3 above)
I could delete it but then the analysis gets complicated when some rows need to be taken into accounts, and some other not - different depending on the analysis.
I could create a column which would be a flag and build a formula around this - it seems awkward, though.
I am looking for something like PowerPoint Hide function: the slide exists but is not part of the presentation. Hiding in Excel does not work that: the row is not visible but still used in calculations.
microsoft-excel worksheet-function
asked Nov 22 at 13:59
WoJ
79731936
I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...)
A
1 10
2 10
3 15
4 27
When making a SUM(A:A) all figures in the column are taken into account.
Is there a way to temporarily disable a row? (say, row 3 above)
I could delete it but then the analysis gets complicated when some rows need to be taken into accounts, and some other not - different depending on the analysis.
I could create a column which would be a flag and build a formula around this - it seems awkward, though.
I am looking for something like PowerPoint Hide function: the slide exists but is not part of the presentation. Hiding in Excel does not work that: the row is not visible but still used in calculations.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Nov 22 at 13:59
WoJ
79731936
asked Nov 22 at 13:59
WoJ
79731936
asked Nov 22 at 13:59
WoJ
79731936
asked Nov 22 at 13:59
WoJ
79731936
asked Nov 22 at 13:59
WoJ
79731936
79731936
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
You could try shifting that row to the right, leaving a blank cell inA3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change15to=Z(15), whereZ()is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, makeZ()return its parameter. This allows you easily to include or exclude several cells at will.
– AFH
Nov 22 at 14:22
add a comment |
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
You could try shifting that row to the right, leaving a blank cell inA3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change15to=Z(15), whereZ()is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, makeZ()return its parameter. This allows you easily to include or exclude several cells at will.
– AFH
Nov 22 at 14:22
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
You could try shifting that row to the right, leaving a blank cell in
A3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change 15 to =Z(15), where Z() is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, make Z() return its parameter. This allows you easily to include or exclude several cells at will.– AFH
Nov 22 at 14:22
You could try shifting that row to the right, leaving a blank cell in
A3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change 15 to =Z(15), where Z() is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, make Z() return its parameter. This allows you easily to include or exclude several cells at will.– AFH
Nov 22 at 14:22
add a comment |
3 Answers
3
active
oldest
votes
If you format your data as a table and sum below values when you hide the rows the values will be removed from the total. You an then reference this total cell elsewhere if you need it to show somewhere else. You can even hide this total cell at the bottom of the data.
I hope this will provide you with some ideas to solve your issue.
answered Nov 22 at 14:16
BradR
564
add a comment |
You could try stacking sums: =SUM(G9:G10)+SUM(G12:G99)
Or you could set the color of the fields you want to exclude, then build a macro that fetches the color: cell.interior.color and then builds a new sum. That will require up front work and I'm not sure if it can be done, but it might be worth a shot: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color
answered Nov 22 at 14:07
Andreas Hartmann
7572820
add a comment |
You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.
answered Nov 25 at 15:05
Geza Kerecsenyi
799
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%2f1377579%2fhow-to-omit-a-row-in-calculations%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you format your data as a table and sum below values when you hide the rows the values will be removed from the total. You an then reference this total cell elsewhere if you need it to show somewhere else. You can even hide this total cell at the bottom of the data.
I hope this will provide you with some ideas to solve your issue.
answered Nov 22 at 14:16
BradR
564
add a comment |
If you format your data as a table and sum below values when you hide the rows the values will be removed from the total. You an then reference this total cell elsewhere if you need it to show somewhere else. You can even hide this total cell at the bottom of the data.
I hope this will provide you with some ideas to solve your issue.
answered Nov 22 at 14:16
BradR
564
add a comment |
If you format your data as a table and sum below values when you hide the rows the values will be removed from the total. You an then reference this total cell elsewhere if you need it to show somewhere else. You can even hide this total cell at the bottom of the data.
I hope this will provide you with some ideas to solve your issue.
answered Nov 22 at 14:16
BradR
564
If you format your data as a table and sum below values when you hide the rows the values will be removed from the total. You an then reference this total cell elsewhere if you need it to show somewhere else. You can even hide this total cell at the bottom of the data.
I hope this will provide you with some ideas to solve your issue.
answered Nov 22 at 14:16
BradR
564
answered Nov 22 at 14:16
BradR
564
answered Nov 22 at 14:16
BradR
564
answered Nov 22 at 14:16
BradR
564
564
add a comment |
add a comment |
You could try stacking sums: =SUM(G9:G10)+SUM(G12:G99)
Or you could set the color of the fields you want to exclude, then build a macro that fetches the color: cell.interior.color and then builds a new sum. That will require up front work and I'm not sure if it can be done, but it might be worth a shot: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color
answered Nov 22 at 14:07
Andreas Hartmann
7572820
add a comment |
You could try stacking sums: =SUM(G9:G10)+SUM(G12:G99)
Or you could set the color of the fields you want to exclude, then build a macro that fetches the color: cell.interior.color and then builds a new sum. That will require up front work and I'm not sure if it can be done, but it might be worth a shot: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color
answered Nov 22 at 14:07
Andreas Hartmann
7572820
add a comment |
You could try stacking sums: =SUM(G9:G10)+SUM(G12:G99)
Or you could set the color of the fields you want to exclude, then build a macro that fetches the color: cell.interior.color and then builds a new sum. That will require up front work and I'm not sure if it can be done, but it might be worth a shot: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color
answered Nov 22 at 14:07
Andreas Hartmann
7572820
You could try stacking sums: =SUM(G9:G10)+SUM(G12:G99)
Or you could set the color of the fields you want to exclude, then build a macro that fetches the color: cell.interior.color and then builds a new sum. That will require up front work and I'm not sure if it can be done, but it might be worth a shot: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color
answered Nov 22 at 14:07
Andreas Hartmann
7572820
answered Nov 22 at 14:07
Andreas Hartmann
7572820
answered Nov 22 at 14:07
Andreas Hartmann
7572820
answered Nov 22 at 14:07
Andreas Hartmann
7572820
7572820
add a comment |
add a comment |
You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.
answered Nov 25 at 15:05
Geza Kerecsenyi
799
add a comment |
You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.
answered Nov 25 at 15:05
Geza Kerecsenyi
799
add a comment |
You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.
answered Nov 25 at 15:05
Geza Kerecsenyi
799
You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.
answered Nov 25 at 15:05
Geza Kerecsenyi
799
answered Nov 25 at 15:05
Geza Kerecsenyi
799
answered Nov 25 at 15:05
Geza Kerecsenyi
799
answered Nov 25 at 15:05
Geza Kerecsenyi
799
799
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%2f1377579%2fhow-to-omit-a-row-in-calculations%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
is it always row 3 you want to omit, is it always just a single row ?
– PeterH
Nov 22 at 14:14
You could try shifting that row to the right, leaving a blank cell in
A3, though I don't know what what this will do to the rest of the spreadsheet calculations. A more complicated edit is to change15to=Z(15), whereZ()is a VBA function which returns zero, regardless of the passed parameter. If you want to include again, makeZ()return its parameter. This allows you easily to include or exclude several cells at will.– AFH
Nov 22 at 14:22