Cumulative Summing

I use cumulative balance columns which add the figure to the left of the cell to the value in the row above the cell. When I insert a row I have to copy the formula from the row above to the new row and to the row below for the column to be correct. Is there a formula that handles inserted rows? I only want to copy the formula down to the inserted row.

By Neale Blackwood

The OFFSET function will allow you to create the formula you require. Assuming column A is for text or date input, column B has the values to be accumulated and column C is the accumulation column. In cell C3 the formula would be:

=B3+OFFSET(C3,-1,0)

Note: the first formula in Column C may need to be different if there is no value above it to add to column B. For example, cell C2 may be =B2 if B1 is the heading for the column.

OFFSET allows you to refer to a cell by starting at a cell and moving, or offsetting, by rows and columns. The -1 means to move a row above the reference, the zero means to stay in the same column. The Help system has a good explanation of the OFFSET function, which also works with ranges.

Extra

Note: you can use the cell’s reference you are in providing the result of the OFFSET doesn’t refer to its own cell. Eg the above formula is in C3 and refers to cell C3 but its doesn’t cause a circular reference error.