How to use an arrayformula to calculate balance on each line

Question

I have a Google sheet which has two columns. Column F has a numeric value in it and I want column G to contain the value of column G in the previous row plus the value of column F in the current row, basically a running total.

I'm trying to use an array formula to do the calculation and it works for the first couple of rows but after that Column G contains the same value as Column F.

The formula I'm using is:

=arrayformula(F3:F10+G2:G9)

What am I doing wrong?

Answer 1

In general, nothing should not use its own output as input. Iterative calculation would be required, and that's a trap-laden path to be avoided whenever there's an established alternative. In this case, the usual approach to a Running Total is MMULT and some garnishing.

If your data starts in F2, put this in G2:

=ArrayFormula(IF(F2:F10,
  MMULT(
    TRANSPOSE((ROW(F2:F10)<=TRANSPOSE(ROW(F2:F10)))*F2:F10),
    SIGN(F2:F10)),
  IFERROR(1/0)
))

Or, slightly more opaque, but able to cope with negative values:

=ArrayFormula(IF(F2:F10,
  MMULT(
    TRANSPOSE((ROW(F2:F10)<=TRANSPOSE(ROW(F2:F10)))*F2:F10),
    IF({F2:F10}<>0,1,0)),
  IFERROR(1/0)
))

Answer 2

a slight alternative would be:

=ARRAYFORMULA(IF(F2:F; 
 MMULT(TRANSPOSE((ROW(F2:F)<=TRANSPOSE(ROW(F2:F)))*F2:F); 
 SIGN(F2:F)^2); IFERROR(1/0)))