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.










share|improve this question






















  • 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

















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.










share|improve this question






















  • 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















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.










share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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 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




















  • 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


















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












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.



Table Formatted Information



I hope this will provide you with some ideas to solve your issue.






share|improve this answer




























    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






    share|improve this answer




























      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.






      share|improve this answer





















        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',
        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
        });


        }
        });














        draft saved

        draft discarded


















        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








        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.



        Table Formatted Information



        I hope this will provide you with some ideas to solve your issue.






        share|improve this answer

























          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.



          Table Formatted Information



          I hope this will provide you with some ideas to solve your issue.






          share|improve this answer























            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.



            Table Formatted Information



            I hope this will provide you with some ideas to solve your issue.






            share|improve this answer












            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.



            Table Formatted Information



            I hope this will provide you with some ideas to solve your issue.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 22 at 14:16









            BradR

            464




            464
























                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






                share|improve this answer

























                  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






                  share|improve this answer























                    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






                    share|improve this answer












                    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







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Nov 22 at 14:07









                    Andreas Hartmann

                    7572820




                    7572820






















                        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.






                        share|improve this answer

























                          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.






                          share|improve this answer























                            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.






                            share|improve this answer












                            You could do =SUM(A:A)-A3 - while it doesn't ignore it in the calculation, it excludes it from the final answer.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Nov 25 at 15:05









                            Geza Kerecsenyi

                            799




                            799






























                                draft saved

                                draft discarded




















































                                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.




                                draft saved


                                draft discarded














                                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





















































                                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







                                Popular posts from this blog

                                Сан-Квентин

                                Алькесар

                                Josef Freinademetz