Search code examples
arraysgoogle-sheetsformula

google gsheets array formula offset (referencing previous row in same column)

I am trying to build a simple banking register in gsheets.

Data looks like this:

Transaction       Debit   Credit   Balance    Formula
opening balance                    100
first             20               80         D3=D2-B3+C3  
second                     30      110        D4=D3-B4+C4

I would like to use array formula, but I am having trouble getting it to handle the reference to another cell in the same column as array formula.

I tried this in cell D3: 

ArrayFormula(D2:D-B3:B+C3:C)

but I get a circular reference error. Any thoughts?

Solution

  • In cell D2, try:

    =ArrayFormula(if(isblank(A2:A),,mmult(transpose(if(transpose(row(B2:C))>=row(B2:C),if(row(A2:A)=2, 100, B2:B*-1+C2:C), 0)),row(B2:C)^0)))

    Note that the 'startbalance' (100) is coded in the formula. Change if needed.