Good Fences Make Good Spreadsheets, Part 1

In the 1980s, a Florida construction company sued Lotus for $250,000 over an alleged defect in their spreadsheet software. The company had prepared a cost estimate for a contract bid by adding various items in a column. They later inserted a new item at the top of the list. The company assumed the sum function would include the new item, but the total did not change and the company ended up miscalculating its costs. Lotus argued that its software worked as described in the manual, and the construction company ultimately dropped its suit.

I read about that case in Lotus Magazine back in the 1980s. The same article taught me some valuable tips, including one for avoiding this kind of problem. The article recommended bracketing a column of numbers with empty border rows, with one at the top and one at the bottom. Make sure the range referenced by the SUM function is based on these border rows. Now the SUM function will automatically adjust to any changes within the border rows, and no items will be dropped accidentally.

As an example, suppose we need to add up three kinds of fruit from cells B2 through B4:

Copyright 2011. All Rights Reserved.

The total is in cell B5, which is highlighted.  Its contents are displayed at upper right.  The SUM function refers to the range of cells from B2 through B4 and it works fine.

But then we remember we have to add in Grapes. Inserting a new row, we have:

Copyright 2011. All Rights Reserved.

You can see that the SUM function still refers to only the original three fruits and missed the grapes.  Fortunately, Excel warns us with a little yellow caution sign that there might be a problem.  Clicking on the warning, we are told the formula omits adjacent cells, and an easy way to fix the problem is offered.

But suppose you have an older version of Excel that doesn’t provide such warnings, or you sometimes ignore warnings since they can be irrelevant? If you would rather avoid the problem in the first place, a good way is to insert border rows above and below the range to be added, and then make sure that the SUM function includes the border rows.  So starting over with the original three fruits, we have:

Copyright 2011. All Rights Reserved.

You can see that the range referenced by the SUM function includes rows 2 and 6. Now, if you need to insert a new row, the SUM function will automatically adjust and the new item will automatically be included in the total:

Copyright 2011. All Rights Reserved.

By the way I shortened the height of the border rows to make for a more attractive report. I used a height of 6 units since anything shorter can hide stray numbers – I don’t want an invisible number skewing the total.

You can also use border columns to bracket rows of numbers that need to be added:

Copyright 2011. All Rights Reserved.

Here, columns B and F serve as brackets for the horizontal totals. Now you can insert additional columns between B and F and be sure that the totals will adjust as they should. Note that I shrunk the width of the border columns to 1.5 units to improve the formatting.

I think of border rows and border columns as fences that help contain and define blocks of data in a worksheet. I have made this my practice for many years and I am sure it has saved me from many errors.

Acknowledgement.

As I was working on this post, I found a terrific article on avoiding SUM errors on Charley Kyd’s ExcelUser site.  In the article, he mentions something about a Lotus Magazine article he had written.  It occurred to me that maybe Charley had written the original article that was so valuable for me.  I had been enjoying some correspondence with Charlie for a couple of months, and so I asked Charley if he had written that article.  Charlie said that he had. So thank you Charlie for all the good you have done me over the years!

Copyright 2011. All rights reserved.

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

See Also: Tables vs. Fences

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