**running balance**, the OFFSET function is a great way to allow you to easily insert and delete rows, without messing up the balance.

To see how this works, take a look at the image below which is a screenshot from a modified version of my Checkbook register template.

To start, let's talk about the formula in column B. In cell B7 let's say I used the formula =B6+1 to increment the number. If I were to delete row 6 I'd get a #REF! error and I'd have to fix the formula. To avoid getting the #REF! error, you can use the following formula where

*this_cell*is the cell containing the formula:

=OFFSET(this_cell,-1,0,1,1)+1

In this example, the OFFSET function is referencing the cell immediately above

*this_cell*. So if

*this_cell*is B7, the formula is essentially the same as =B6+1. But now if you delete row 6, you don't get a #REF! error.

This same concept works for the

**running balance**in column J. The formula for the balance in cell J7 would normally be =J6+I7-H7. However, just as in the previous example, if you were to delete row 6, you'd get a #REF! error. So instead you can use the OFFSET function:

=OFFSET(J7,-1,0,1,1)+I7-H7

I've used this technique in a number of my templates because it makes the spreadsheet easier to use. The problem with this approach is that it makes the formulas more difficult to debug, because it looks like the formula is referencing itself.

## 4 comments :

nice one

should'nt it be =I6+I7-H7 instead of =B6+I7-H7

Your instructions seem clear and applicable for me. I will try to do it.

thank you!!!

Post a Comment