Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
and what do you want to achieve? –  JMax Dec 3 '11 at 13:37
add comment

migrated from superuser.com Dec 3 '11 at 13:09

This question came from our site for computer enthusiasts and power users.

1 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 >=.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.