Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

So I have the following situation:

enter image description here

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?

share|improve this question
1  
Perhaps this answer helps you further: webapps.stackexchange.com/a/37839/29140 –  Jacob Jan Tuinstra Jan 11 '13 at 11:37
add comment

3 Answers 3

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

share|improve this answer
1  
Thanks for the reply, but that's not quite what I asked for. The fact my data is composed of sequential numbers and the fact I apply a sum to them is just for sake of example, but both the data and the function could be anything. Your approach is specific to the example case I gave, and doesn't even use the first column as a source of data, in fact ROW(A:A) expands to a sequence of increasing numbers which is independent of what A:A contains at all. –  Fabio A. Jan 3 '13 at 17:56
    
Oh, I see you've changed your response, now my comment above looks dumb. The GAS approach could be certanly reused for other other kind of operations (not just the sum, which I restate was just an example), but it wouldn't work for functions that are available only within the spreadsheet (think of slope() and intercept(), which are the ones I actually need). I would have to reimplement those functions in javascript. –  Fabio A. Jan 4 '13 at 1:47
    
@FabioA. Please show me an example were you use slope() and ` intercept()` (the dumb way). I'm glad I was able to get the running total. There are not many example's to find on the internet.... –  Jacob Jan Tuinstra Jan 4 '13 at 5:21
add comment
=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).

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.