How to omit a row in calculations?
up vote
0
down vote
favorite
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
78331836
add a comment |
up vote
0
down vote
favorite
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
78331836
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 |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
78331836
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
78331836
asked Nov 22 at 13:59
WoJ
78331836
asked Nov 22 at 13:59
WoJ
78331836
asked Nov 22 at 13:59
WoJ
78331836
asked Nov 22 at 13:59
WoJ
78331836
78331836
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
up vote
1
down vote
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
464
add a comment |
up vote
0
down vote
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 |
up vote
0
down vote
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 |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
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
464
add a comment |
up vote
1
down vote
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
464
add a comment |
up vote
1
down vote
up vote
1
down vote
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
464
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
464
answered Nov 22 at 14:16
BradR
464
answered Nov 22 at 14:16
BradR
464
answered Nov 22 at 14:16
BradR
464
464
add a comment |
add a comment |
up vote
0
down vote
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 |
up vote
0
down vote
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 |
up vote
0
down vote
up vote
0
down vote
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 |
up vote
0
down vote
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 |
up vote
0
down vote
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 |
up vote
0
down vote
up vote
0
down vote
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