Good Fences Make Good Spreadsheets, Part 2

Good Fences Make Good Spreadsheets, Part 1 discussed the idea of using border rows or border columns to hold blocks of data for use by the SUM function. The borders assure that the SUM function will include any new cells that are inserted into the block.

In this post, I describe an additional benefit of borders. Let’s begin with a short loan schedule:

There is an error in the interest payment formula which affects most of the other calculations. Interest is paid monthly but here the formula is for annual interest.

Let’s try to fix the mistake. First, divide the formula in D4 by 12 to reflect monthly interest:

Now we just need to copy D4 down the column, so that all of the interest payments will be correct. A quick way to do this is to place your cursor over the tiny black square (the “Fill Handle”) at the lower right corner of the highlighted cell, and then double-click to Auto Fill down the column. Another quick way to copy the cell is to use CTL-C, followed by [shift][end][down arrow][enter]. With either quick copying method, we now have:

Unfortunately, the copies did not stop with the interest payments but ran down to the total interest cell at the bottom. Now D8 holds the formula for an interest payment instead of a SUM calculation. Auto Fill and other quick copying methods do not stop until they encounter a blank cell. Since there was no blank before the total, it was overun by the wrong formula.

This kind of mistake may be easy to spot here, but it can be harder to catch in a large spreadsheet, as I have learned the hard way.

But what if we had originally included border rows above and below the block of calculations? We could have easily fixed the interest calculation and copied it down without corrupting the total interest:

So in addition to making sure that SUM functions always refer to newly inserted cells, border rows have the advantage of reducing copying errors. Border columns can provide a similar benefit.

Copyright 2011. All Rights Reserved.

Related Posts: Good Fences Make Good Spreadsheets, Part 1; Part 3; A Big, Avoidable Mistake

See Also: Tables vs. Fences

Advertisements
This entry was posted in Spreadsheet Tips and tagged . Bookmark the permalink.

5 Responses to Good Fences Make Good Spreadsheets, Part 2

  1. Pingback: Good Fences Make Good Spreadsheets, Part 1 | The Well-Tempered Spreadsheet

  2. Pingback: Tables vs. Fences | The Well-Tempered Spreadsheet

  3. Pingback: Good Fences Make Good Spreadsheets, Part 3 | The Well-Tempered Spreadsheet

  4. Pingback: A Big, Avoidable Mistake | The Well-Tempered Spreadsheet

  5. Pingback: Fast Formulas #3: The Spreadsheet | The Well-Tempered Spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s