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

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?

``````function CUMSUM(array) {

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

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

// convert 2D array to 1D array

// 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) {
} else {
}
}

// 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

• 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. Jan 3, 2013 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. Jan 4, 2013 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.... Jan 4, 2013 at 5:21
``````=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).

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