Announcement

Collapse
No announcement yet.

Excel Question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Question

    Some of you have to be excel experts. I want to create a spreadsheet that will automatically add the totals in a column, even if I add a new row. Like so:

    Row1: data data data data 100 data data data
    Row2: data data data data 199 data data data

    it's easy enough to create Row3 and have cell 3x5 be =1x5+2x5. But is there a way to make it so that if I want to add a new Row3, bump my totals down to Row4, and I don't have to manually say that cell 4x5 =1x5+2x5+3x5?

    Thanks

  • #2
    Re: Excel Question

    Originally posted by AMC View Post
    Some of you have to be excel experts. I want to create a spreadsheet that will automatically add the totals in a column, even if I add a new row. Like so:

    Row1: data data data data 100 data data data
    Row2: data data data data 199 data data data

    it's easy enough to create Row3 and have cell 3x5 be =1x5+2x5. But is there a way to make it so that if I want to add a new Row3, bump my totals down to Row4, and I don't have to manually say that cell 4x5 =1x5+2x5+3x5?

    Thanks
    you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.

    Comment


    • #3
      Re: Excel Question

      i dont follow completely.. but if you use the sum(A1:A10) function, everytime you add a row in that range it will include it in the total.

      edit

      Originally posted by jen View Post
      you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.
      yah what she said
      “Unless you’re Boston College, you don’t get here every year.”

      Pinhead Nation: "gone" but not forgotten

      Originally posted by shrader
      Admin, do you still hate BC? If not, will darin and MAV ever be freed? If you do still hate BC, why is SteveF allowed to post?
      Hockey East Champions: 1987, 1990, 1998, 1999, 2001, 2005, 2007, 2008, 2010, 2011, 2012
      National Champions: 1949, 2001, 2008, 2010, 2012

      Comment


      • #4
        Re: Excel Question

        Originally posted by jen View Post
        you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.
        I don't know if that is a setting/option but that isn't how 2007 works for me. If you add a new row 3, moving the sum(e1:e2) formula to row 4, then the formula stays sum(e1:e2).

        We've just grown accustomed to leaving a blank line and then putting the formula in row 4 to start. So

        a1 Data
        a2 Data
        a3 blank
        a4 Formula sum(a1:a3)


        Then if you add a line below row 2 it automatically changes the formula to account for the new line.
        I believe in life, and I believe in love, but the world in which I live in keeps trying to prove me wrong.

        Comment


        • #5
          Re: Excel Question

          Your best bet to solve this is to create a simple VB function in Excel that you can call in your spreadsheet.

          The function could iterate through the column you are totalling until it found the last entry. Then it would know how many rows to add together for the total.
          PSNetwork / XBOX GamerTag: xJeris
          Steam Profile

          Sports Allegiance
          NFL: CHI; MLB: MN, NYM; NHL: MN, MTL; NCAAB: MN, UNLV; NCAAF: MN, MIA; NCAAH: MN; Soccer: USA, Blackburn

          Comment


          • #6
            Re: Excel Question

            Originally posted by pirate View Post
            I don't know if that is a setting/option but that isn't how 2007 works for me. If you add a new row 3, moving the sum(e1:e2) formula to row 4, then the formula stays sum(e1:e2).

            We've just grown accustomed to leaving a blank line and then putting the formula in row 4 to start. So

            a1 Data
            a2 Data
            a3 blank
            a4 Formula sum(a1:a3)


            Then if you add a line below row 2 it automatically changes the formula to account for the new line.

            How do you insert the new line? Using "shift cells down" or "entire row"?

            Using - right click - insert - entire row - it works.

            Then I tried using right click - insert - shift cells down - and it did NOT work.

            Comment


            • #7
              Re: Excel Question

              Originally posted by jen View Post
              How do you insert the new line? Using "shift cells down" or "entire row"?

              Using - right click - insert - entire row - it works.

              Then I tried using right click - insert - shift cells down - and it did NOT work.
              This worked. Thank you.

              Comment


              • #8
                Re: Excel Question

                Originally posted by JF_Gophers View Post
                Your best bet to solve this is to create a simple VB function in Excel that you can call in your spreadsheet.

                The function could iterate through the column you are totalling until it found the last entry. Then it would know how many rows to add together for the total.
                Re-read the question that was asked? Do you think this is a user who knows how to write VB scripts or use the macro writer?

                If in doubt about whether the formula will adjust when inserting a row at the end, insert the row in the middle instead. You're guaranteed that the formula will adjust to your needs.

                Also, simply use the Autosum function in the toolbar - the sigma button that looks like this: ∑. Double-click it when your active cell is at the end of a list of numbers and the summation formula will automatically populate that active cell.
                "The party told you to reject the evidence of your eyes and ears. It was their final, most essential command." George Orwell, 1984

                "One does not simply walk into Mordor. Its Black Gates are guarded by more than just Orcs. There is evil there that does not sleep, and the Great Eye is ever watchful. It is a barren wasteland, riddled with fire and ash and dust, the very air you breathe is a poisonous fume." Boromir

                "Good news! We have a delivery." Professor Farnsworth

                Comment


                • #9
                  Re: Excel Question

                  i found another great solution thats also really simple.

                  In the cell you want to be your total create a variable range. For example if your total was in A11 and your data column was A.

                  - In Excel 2007 you can just right-click the cell and select "Name a range".
                  - Give it a name like "prevCell".
                  - In the Refers To field put: =A10 (or =Sheet1!A10) without the $ signs.
                  - Click "OK"
                  - Now in A11 put your SUM formula: =SUM(A1revCell)

                  No matter where you add a row it will keep adding all the of the A cells above your total. Even if you insert/shift a cell and not a row.
                  Last edited by JF_Gophers; 01-27-2010, 09:08 AM.
                  PSNetwork / XBOX GamerTag: xJeris
                  Steam Profile

                  Sports Allegiance
                  NFL: CHI; MLB: MN, NYM; NHL: MN, MTL; NCAAB: MN, UNLV; NCAAF: MN, MIA; NCAAH: MN; Soccer: USA, Blackburn

                  Comment


                  • #10
                    Re: Excel Question

                    Originally posted by JF_Gophers View Post
                    i found another great solution thats also really simple.

                    In the cell you want to be your total create a variable range. For example if your total was in A11 and your data column was A.

                    - In Excel 2007 you can just right-click the cell and select "Name a range".
                    - Give it a name like "prevCell".
                    - In the Refers To field put: =A10 (or =Sheet1!A10) without the $ signs.
                    - Click "OK"
                    - Now in A11 put your SUM formula: =SUM(A1revCell)

                    No matter where you add a row it will keep adding all the of the A cells above your total. Even if you insert/shift a cell and not a row.
                    Hmm.... will this work if I add cells right above my bottom row? Like if I have rows A-G, and then add a new G, so my new totals row is H. My "G" is now blank. So would it catch anything in G if my "prevCell" is =F10 now?

                    Does that make sense?

                    jen's solution is kind of working, but not perfectly. It seems like this would be the kind of thing that you would be able to do easily, I can't be the only person that has ever wanted to do this.

                    Comment


                    • #11
                      Re: Excel Question

                      Originally posted by AMC View Post
                      Hmm.... will this work if I add cells right above my bottom row? Like if I have rows A-G, and then add a new G, so my new totals row is H. My "G" is now blank. So would it catch anything in G if my "prevCell" is =F10 now?

                      Does that make sense?

                      jen's solution is kind of working, but not perfectly. It seems like this would be the kind of thing that you would be able to do easily, I can't be the only person that has ever wanted to do this.
                      It will work no matter where you add the row or column as long as its before the total field.

                      If your total is moving to the right then the prevCell would be to the "left" of the total (i.e., adding columns).

                      If your total is moving down (i.e., adding rows), then the prevCell would be "above" the total.

                      The reason being that Excel is automatically adjusting your formula for you as you add rows/columns.
                      If you start with =A10 and add a row, that cell will now say =A11. This is why you are not using the $ sign, as that would lock the cell reference to $A$10.
                      Last edited by JF_Gophers; 01-27-2010, 12:48 PM.
                      PSNetwork / XBOX GamerTag: xJeris
                      Steam Profile

                      Sports Allegiance
                      NFL: CHI; MLB: MN, NYM; NHL: MN, MTL; NCAAB: MN, UNLV; NCAAF: MN, MIA; NCAAH: MN; Soccer: USA, Blackburn

                      Comment


                      • #12
                        Re: Excel Question

                        I like the sound of it, but I have Excel 2008 Mac edition, when I right click on a cell I don't get a Name a Range option.

                        I tried to search for it in Excel's help files but found nothing.

                        Comment


                        • #13
                          Re: Excel Question

                          Originally posted by AMC View Post
                          I like the sound of it, but I have Excel 2008 Mac edition, when I right click on a cell I don't get a Name a Range option.

                          I tried to search for it in Excel's help files but found nothing.
                          In Help you want to look for "Define Name" or "Name Manager"

                          Its probably under Insert --> Name --> Define, or you can try the key code [Command]+[F3]
                          Last edited by JF_Gophers; 01-27-2010, 02:01 PM.
                          PSNetwork / XBOX GamerTag: xJeris
                          Steam Profile

                          Sports Allegiance
                          NFL: CHI; MLB: MN, NYM; NHL: MN, MTL; NCAAB: MN, UNLV; NCAAF: MN, MIA; NCAAH: MN; Soccer: USA, Blackburn

                          Comment


                          • #14
                            Re: Excel Question

                            Originally posted by JF_Gophers View Post
                            In Help you want to look for "Define Name" or "Name Manager"

                            Its probably under Insert --> Name --> Define, or you can try the key code [Command]+[F3]
                            Working perfectly, thank you so much.

                            Comment


                            • #15
                              Re: Excel Question

                              What I typically do in a case like that is to put the sum in the top row. So, for example, in Excel, in cell A1, I enter

                              =sum(A2:An)

                              where "n" is the largest number I will possibly use. If it's going to me a very large number (e.g. n=2000), I split the window, so row 1 and rows 1975 - 2000 are in the window.

                              Comment

                              Working...
                              X