Summing cells in a row

[myDavey]

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

[Jump_Over · Accepted solution]

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

[myDavey]

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

[Jump_Over]

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

[myDavey]

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

[myDavey]

Ok, just tried and approved

Works great!

And, now I understand what the second loop was for

Thanks a lot

Davey

[M.Hasanin]

Hi, I Changed the Script to Calculate the Columns also, but it act differently when selectiong more than one column!  , it only calculate one column in one time! not like the row version of the code that works if you select one row or select many rows it will still accurate in the calculating sums (row or rows!) , so what shouid i change here!  and thanks in advance :

//Summing cells in a Columns
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 = prevCell.parentColumn.cells.lastItem(prevCell); 
  while (prevCell && currCell.parentColumn == prevCell.parentColumn && len-->0) {
       currSum +=  Number(prevCell.contents); //SUM
       currCell = mT[len];
       prevCell = mT[len-1];
      }
  sumCell.contents = currSum.toString();
  }

[Laubender]

Hi M.Hasanain,

if all selected cells contain "reasonable" values, one could do this:

eval( app.selection[0].contents.join("+") );

 But this could be a very naive approach. No safety net at all.

Warning: Usually you would better test the contents of every single cell before doing the processing.

Regards,
Uwe Laubender

( ACP )