Q. 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.

A. 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.

Neale Blackwood CPA is a senior business analyst with Access Analytic Solutions, which provides Excel consulting services. You can email Neale at nblackwood@accessanalytic.com.au