Google Spreadsheet ArrayFormula where the value depends on the values above it

Question

I'm looking for a solution to an ArrayFormula problem where the value I put into column B depends on all of the values of column B above it. (It's not a real circular reference, I can write the solution without an ArrayFormula).

Here's a simple version of what I'm trying to do. Cells in column A contain positive or negative value. If the value in A is positive, the same value will be copied to B. However, if A is negative, the value of A copied to be will be the minimum of either A or the cumulative sum of B for all the cells above it.

A     B
  1     1
  2     2
 -2    -2
  3     3
-10    -4

This the formula in B2 that I manually copied down, that does this:

=if(A2>0,A2,max(-sum(B$1:B1),A2))

you could view A as a request to either put money in an account (+) or take money out of an account (-) and B is the response to what happens, it either all goes in, or you can pull the max out.

I'm happy to have additional columns to do intermediate calculations if necessary, but I just can't seem to figure out a way to get around the pseudocircular reference where the value to put in Bn depends on Bn-1.

Ideally I'd love to have it set up so that my ranges in the ArrayFormula would be of the form B2:B so that I don't have to worry about ever extending them when the data grows.

Answer 1

below code should provide you with a custom function that kinda behaves like an arrayformula. You can reference a whole column with it. Click here for an example sheet (Cell C2 holds the custom formula).

The code used is this:

function CF(range) {
var out = [];
range.reduce(function (a, b) {
    return a.concat(b);
})
    .filter(function (d) {
        return d !== '';
    })
    .forEach(function (x) {
        out.push(x > 0 ? x : Math.max(x, -out.reduce(function (p, c) {
            return p + c;
        })))
    });
return out;
}

I hope this helps ?