How to apply a function to all the rows preceeding the current one in an arrayformula?

Question

So I have the following situation:

In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding rows (plus itself) in the 1st column. In this case the function applied is SUM().

The dumb approach is the one I used: write the formula in the first row, then copy&paste down till the row you want the function to be applied to.

I want it to be automated, though, and function with as many rows in the given column as there are, even if they are removed or added. I thought I'd use ARRAYFORMULA() for this, or MMULT() combined with TRANSPOSE() as I've seen doing in apparently related examples, but after quite a few tries I'm lost.

Does anybody know how it can be done?

Answer 1

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total

Answer 2

=SUM(B1+A2)

Add this to all cells in column; it adds the above number to left number of cell. Doesn't use a script and can be applied to a column at once (just skip the first cell).

Answer 3

I think what you want may be:

for slope: =slope(A$1:A1,B$1:B1)
for intercept: =intercept(A$2:A2,B$2:B2)

though both the above need two sets of data, and based on your question:

for sum: =sum(A$2:A2)

in all cases copied down to suit (including into any added rows).