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.

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.

"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"... do you mean maximum? — AdamL
It's the biggest negative number I can subtract. It's hard to say in English, is that min, max, farthest from zero? — miked
I am not sure if I understand your thinking behind how you benefit from using an Array Formula.. Array formula is generally used where you want one value from similar operation performed on several elements of more than one arrays. You will need to enter a formula in every cell in column B, so how do you think array formula will help you? It could be just me, that I don't understand it. — Vikas Gupta
What I like about the array formula is that I can automate a bunch of my sheets easily. I don't have to copy down formulae when I add new data. For many of my sheets, I have a worksheet set as the "input sheet" where data is added. Then I have a bunch of hidden "calculation" sheets full of array formulae, and then a result sheet at the end. This is just one of the computations that I haven't figured out how to array-ify so that I can hide it over in my calculation sheets. — miked
Ah OK I understand what you mean. However, what would happen on the next line? Does the "outstanding" request of -6 need to be fulfilled, or is it ignored thereafter? — AdamL

JPV JPV · Accepted Answer · 2014-12-10T10:59:03

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 ?

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

1 Answers