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.

### Like this:

Like Loading...

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

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

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

There are other techniques as well. Using named ranges (possibly dynamic) could also cut down on these types of errors. Also, building the formula by using Excel’s built-in formula builder (the AutoSum button) would have included that cell in the first place.

LikeLike

Thanks Jimmy. There was some discussion of dynamic ranges at the Excel Blackbelts group on LinkedIn this morning (membership required):

http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=46064617&gid=3124035&commentID=50181402&goback=%2Enmp_*1_*1_*1_*1_*1_*1%2Egmp_3124035%2Eamf_3124035_25877950&trk=NUS_DISC_Q-subject#commentID_50181402

but we hadn’t talked about dynamic range NAMES. You might name a range something like:

INDEX($A:$A,ROW($A$10)+1):INDEX($A:$A,ROW($A$20)-1)

where A10 is the header and A20 is the total.

I think dynamic range names can be a good idea, but they could be time-consuming (and you could get range name overload) if you need to do this many times in a big model.

As for AutoSum, that gets you a good formula for the original list, but if you enter a new number above the list later on, it will not be included in the total unless you remember to press AutoSum again. If you have are in the habit of updating AutoSum when the list changes then you should be fine, but otherwise you might have trouble.

Thanks again for the comment!

LikeLike

To amend my previous comment, if you replaced the expression above with

INDEX(A:A,ROW(A$10)+1):INDEX(A:A,ROW(A$20)-1)

then you would have a range name that is relative with respect to columns, and that you could copy horizontally for as many columns as you need. That would be pretty slick.

(I revised this comment)

LikeLike

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