Arrayformula to preserve a running/cumulative balance when inserting new rows-CodePudding

The top right cell (Natwest) is a list from a range using data validation.

The Opening Balance 1,000.00 is sourced from another sheet using a lookup formula.

Using simple if statements, the cumulative balance is then produced - according to the Amount column and whether the Natwest account occurs in the Dr( ) or Cr (-) column

i.e. =if(B4=$D$1,D3 A4,if(C4=$D$1,D3-A4,D3)) and copied down.

                                Natwest
Amount  Dr          Cr          Balance
                                1,000.00
100.00  Natwest     Account 1   1,100.00 
200.00  Account 2   Natwest     900.00 
400.00  Natwest     Account 1   1,300.00

It works fine, except that when a new row is inserted, the if statement formula is not copied into the new row.

I am looking for an arrayformula solution (or other formula inside the cell solution), so that the Cumulative Balance still works, but doesn't need to be copied into column D new row - when a new row(s) are inserted.

(I don't mind the Natwest (drop down from the list) or the Opening Balance 1,000.00 to be moved elsewhere if required for a solution.)

Thanks for your help.

CodePudding user response：

Something adding up in between the same range of the arrayformula is always going to be tricky with circular dependency. I suggest to get the initial value and add it the SUMIF of second column and substract the SUMIF of second column up to each value. With BYROW you can do it like this:

=BYROW(A4:A,LAMBDA(each,SUMIF(INDIRECT("B4:B"&ROW(each)),D1,A4:each)-SUMIF(INDIRECT("C4:C"&ROW(each)),D1,A4:each) D3))

CodePudding user response：

Alternate solution:

You can use this custom function from AppScript for automatically calculating cumulative balance

Code:

function customFunction(startnum, key, range) {
  var res = [];
  var current = startnum;
  range.forEach((x) => {
    res.push(x.map((y, index) => {
      return y == key && index == 1 ? current = (current   x[0]) : (y == key && index == 2 ? current = (current - x[0]) : null)
    }).filter(c => c))
  })
  return res;
}

Custom Function Parameters:

=customFunction(startnum, key, range)

startnum = opening balance

key = Account name

range = cell range

Sample output:

=customFunction(D3,D1,A4:C)