A Big, Avoidable Mistake

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.

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

7 Responses to A Big, Avoidable Mistake

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

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

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

  4. JP says:

    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.

    Like

  5. Win Smith says:

    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!

    Like

  6. Win Smith says:

    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)

    Like

  7. 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