Google-sheets – Apply Formula to Newly Inserted Row

google sheets

I have a spreadsheet that keeps a running balance of a credit card with the newest transactions at the top (just like credit card web sites).

enter image description here

An example of the formula for each cell in the Balance column is 

=$F$16+SUM(OFFSET($F13, 0, -1, ROWS(E13:E15)-(ROW()-ROW($F$12)-1)))

Everything works fine except as I insert new rows to add transactions, I have to manually fill the formula up. I'm trying to find a way to apply the formula for all rows in the column. I was able to do this with Numbers. I've looked into =ARRAYFORMULA but I can't get the syntax right.

I'd like to be able to insert new rows and have the balance column automatically populated w/ the formula.

Best Answer

The solution below involves a very elegant and generally useful technique popularized by ahab, a Google Docs expert; I suppose he invented it. It uses matrix multiplication.

Move the label "Starting" to the last cell of the Description column, column D.

Move the starting balance to the last cell of the Amount column, column E.

Insert a new blank row 2. This row will be reserved and should be left blank except for the following formula, so you might want to freeze it along with row 1.

Put the following formula in cell F2.

= ArrayFormula( if( isnumber(E2:E)
      ;
    mmult(
      transpose(E2:E) * (row(E2:E) <= transpose( row(E2:E) ) )
        ;
      sign( row(E2:E) )
    )
      ;
) )

Any row having a transaction amount in column E now has a corresponding running balance in column F. The running balance has the desired property that the initial balance is in the last row of column F and subsequent balances read up column F.

If, instead, you want your starting balance in the first (data) row of column F (cell F2) and subsequent balances to read down the column, change <= to >=.

Related Topic