Spreadsheet Errors

Here’s a list of publicly-reported spreadsheet mistakes, compiled by the European Spreadsheet Risk Interest Group.

These stories illustrate common problems that occur with the uncontrolled use of spreadsheets. We say how we think the problem might have been avoided.

An obvious form of risk avoidance is simply to check your work before sending it out. For important spreadsheets, a second pair of eyes (‘peer review’) is even better. Where stakes are high, a thorough test and audit is a further defence.

How about some personal anecdotes. Have you ever been embarrassed by a serious spreadsheet error?

Bible Workbook

File this under “The last thing you’d expect from John Walkenbach.” It’s the Bible in an Excel workbook.

I found a text file with the complete King James Version of the Bible. I wrote a few macros and dumped it all into an Excel workbook. Each book is on a separate worksheet, and each verse is in a separate cell.

It has a handy hyperlink table of contents so you can jump to any book. I also wrote some summary formulas to calculate the number of characters in each book, and the average number of characters per verse. Then I added a word count feature: Enter a word and it displays the number of occurrences in each book. Finally, I set up a User Form that displays a random verse.

You can download it here. It’s a 1.8 Mb zip file, and it expands to a 6.1Mb Excel workbook. Even if you have no interest in the content, you may discover a few useful techniques. You’ll need to enable macros to use the random verse feature.

We May Now Discuss Excel 12

I found this at Ed Bott’s blog: No more veil of secrecy over Office 12.

Josh at Windows Connected says he heard from Microsoft that it’s now OK to write about Office 12 Beta 1 client applications.

I checked in with Frank Shaw at Waggener-Edstrom, Microsoft’s PR agency, and he confirms that it’s true. Anyone on the Office 12 beta program is free to write about and publicly discuss any of the client applications in the suite. (Server components of Office 12 are still under NDA, however.)

That’s certainly good news. Excel 12 has some great new features, but it also has some serious problems. My biggest gripe is the inability to modify the user interface. In other words, no more menu modifications and no more toolbars. I’m hoping that they will do something about this problem in Beta 2, but I’m not holding my breath.

But the real problem with Office 12 is that it will require massive re-training. Even experienced users should plan on weeks or months to feel comfortable with the new UI and get up to speed. But even then, many of the things we do very efficiently today will become less efficient with Excel 12.

Excel Vulnerability For Sale

Here’s something I haven’t seen before. Up for auction at eBay: Brand new Microsoft Excel Vulnerability.

The vulnerability was discovered on December 6th 2005, all the details were submitted to Microsoft, and the reply was received indicating that they may start working on it. It can be assumed that no patch addressing this vulnerability will be available within the next few months. So, since I was unable to find any use for this by-product of Microsoft developers, it is now available for you at the low starting price of $0.01 (a fair value estimation for any Microsoft product).

More specifically:

Microsoft Excel does not perform sufficient data validation when parsing document files. As a result, it is possible to pass a large counter value to msvcrt.memmove() function which causes critical memory regions to be overwritten, including the stack space. The vulnerability can be exploited to compromise a user’s PC. It is feasible to manipulate the data in the document file to get a code of attacker’s choice executed when malicious file is opened by MS Excel. The exploit code is not included in the auction. You must have very advanced skills if you want to further research this vulnerability.

Part of the terms and conditions:

You may not use this information for malicious or illegal purposes. The information you receive is for educational and research purposes only.

Multi-Threaded Calc In Excel 12

David Gainer discusses Multi-threaded calculation in Excel 12.

One of the things I mentioned in my overview of Excel 12 post in September was that we had done some work to speed up calculation on modern hardware (multi-processor or dual-core chips). I thought I would take a brief break from tables to summarize that work and to see if any readers are interested in beta testing this feature.

He’s also looking for beta testers:

The Excel team has reserved a number of positions in our upcoming technical beta for individuals that have the interest, time, hardware, and spreadsheets to help test the work we have done in this area (and all the other work we have done, although we are looking for a few people that are particularly interested in calculation performance).

Invoice App: The Results

Last week I asked readers to create a simple invoice application using the specifications I listed. See: Your Assignment: Create A Simple Invoice App.

Nine people (including me) responded:

  • Kirk Anderson
  • Per Arnader
  • Fadi Chalohi
  • Ken Clifton
  • Donald R. Cossitt
  • Doug Glancy
  • Aurelio L’Ambrosa
  • John Walkenbach
  • Charlene Wright

You can download all nine workbooks (zip file). I changed the workbook names so they correspond to the author names. Some of the files contain macros, and they are all virus-free.

As you might suspect, there were lots of different approaches — ranging from very simple to very complex. I think we can all learn a few things by studying these apps.

I invite everyone who responded to leave a comment here with a brief description of how you approached the problem.

Here’s how I approached it:

I used Data Validation to allow the user to select the products from a list and specify the quantity. I used a macro to jump to the Quantity column after an item is entered. Another macro jumps back to the Item column when a Quantity is entered. This isn’t necessary, but I thought it was a nice touch.

Unit costs are obtained using a VLOOKUP formula. I also included a checkbox (labeled “Allow item & price overrides”), linked to a simple macro. When checked, the user can enter a product that’s not on the list, and also overwrite the VLOOKUP formula with a different price. A button at the top of the form clears the invoice, and also regenerates the formulas.

In order to determine whether sales tax should be added, I used separate cells for the city, state, and zip. I used a Data Validation Input Message to help identify these cells. Using separate cells for the address (at the top) required that I use merged cells down below.

This app is very simple, but it meets all of the original requirements. And I’m positive that it would take less than five minutes for a user to get up to speed.

 Thanks to all who participated. Hopefully, this will generate some good discussion.