5 Replies Latest reply on Jun 24, 2014 5:12 PM by myDavey

    Summing cells in a row

    myDavey Level 1

      Hi

      I have a table with many rows

      Sometimes I have to choose a few cells from a few columns and do some mathematical equation, and write into another cell

      E.g. I select columns 3-5 in rows 8-20

      I want to row by row get sum of the 3 columns selected, and the print it into the next column - column 6

       

      Is there an easy way to work with the cells selected in this situation

      Or do we have to iterate through the cells until we find which ones are selected and getting the row# and column# of the table

       

      If someone can help me we this I'd appreciate it

       

      Thanks

      Davey

        • 1. Re: Summing cells in a row
          Jump_Over Level 5

          Hi,

           

          Solution can be based on:

              1. iterating through selected cells

              2. detecting row's break

              3. place current sum to a cell next to the last of row in the selection

           

          I.e. like this:

           

          var
            mT = app.selection[0].cells.everyItem().getElements(),
            currSum, sumCell,
            len = mT.length,
            currCell, prevCell;
          
          while (len-->0) {
            currCell = mT[len];
            prevCell = mT[len-1];
            currSum = Number(currCell.contents);
            sumCell = currCell.parentRow.cells.nextItem(currCell);
            while (prevCell && currCell.parentRow == prevCell.parentRow && len-->0) {
                 currSum +=  Number(prevCell.contents);
                 currCell = mT[len];
                 prevCell = mT[len-1];
                 }
            sumCell.contents = currSum.toString();
            }
          

           

          Jarek

          • 2. Re: Summing cells in a row
            myDavey Level 1

            Hi Jarek

            Thanks a lot

            It was the 'nextItem' method that I was missing!

             

            Now, I understand everything you did here except the second loop

            I didn't test it, but why is the second loop needed?

             

            Also, little of a side point

            I wasn't using the 'everyItem().getElements()' here

            I never got it straight when its used and when not

            Can you please explain that?

             

             

            Thanks

            Davey

            • 3. Re: Summing cells in a row
              Jump_Over Level 5

              Hi,

               

              Second loop is for calculating a partial sum (from break to break)

               

              everyItem().getElements() returns an array of objects which is static and faster to iterate through.

              Otherwise you are iterating through an dinamic collection which is much more time consuming.

               

              Jarek

              • 4. Re: Summing cells in a row
                myDavey Level 1

                Thanks

                I guess I don't fully understand the method of your function yet

                I will try it now to see better how it works

                 

                Regarding the everyItem().getElements()

                So basically its just faster, and therefore it would always be better to use it (unless we're too lazy to type the extra words )

                 

                 

                Thanks a lot

                Davey

                • 5. Re: Summing cells in a row
                  myDavey Level 1

                  Ok, just tried and approved

                  Works great!

                  And, now I understand what the second loop was for

                   

                  Thanks a lot

                  Davey