Yesterday, I found a multi-million dollar error in a spreadsheet that had been prepared by someone else. The spreadsheet’s author had employed some of Excel’s most advanced features, but had not followed a simple practice that would have prevented the mistake.
There were several numbers in a vertical list, with a total at the bottom. Unfortunately, the SUM function at the bottom reflected all of the items except the first one, which was in the millions. This actually was not an obvious error in the context of this model. I found it as I was testing some changes I was making.
What probably happened is that there was an original list that changed into the final list. The original total was correct because it included the original list. Later, the new number was inserted at the top of the list. Maybe the author assumed that the argument for the SUM function would automatically adjust itself to reflect the expanded range of numbers. Maybe the author knew the function had to be adjusted but forgot to make the change. Either way, the total did not reflect the new item.
In Good Fences Make Good Spreadsheets, Part 1, Part 2, and Part 3, I advocated a simple way to avoid this kind of error. The idea, which goes back to the early days of computerized spreadsheets, is to bracket the block of numbers with blank cells on either side of the list, and then apply the SUM function to the range that spans the blank cells and the numbers between them. This way, any items that join the list later will always be between the blank cells, and so they will be included in the total automatically.
The blank cells act like “fences” that help define the block of numbers to be totaled. With numbers to be added vertically, the fences can be built by inserting blank rows above and below the list.
Unfortunately, I don’t see fences used very often. How many other huge errors are hiding out there that could have been easily prevented?
Note: I should mention that there is another technique for managing blocks of data in Excel called the “Table” (as distinct from the “Data Table”). Tables have many advantages including the automatic updating of SUM calculations, but they seem to be more appropriate for blocks of data values than for blocks of calculations (see Tables vs. Fences). I don’t think a Table would have worked with the format for the model described above. I will discuss Tables further at some point.
Copyright 2011. All rights reserved.