1% of all formulas in operational spreadsheets are in error.
Powell, Baker, & Lawson (2009)
A lot of decisions are being made on the basis of some bad numbers.
Ross (1996)
Spreadsheets can be viewed as a highly flexible programming environment for end users.
Abreu, et al (2015)
Most executives do not really check or verify the accuracy or validity of [their] spreadsheets...
Teo & Tan (1999)
Spreadsheet errors have resulted in huge financial losses.
Abraham & Erwig (2007)
Spreadsheet shortcomings can significantly hamper an organization's business operation.
Reschenhofer & Matthes (2015)
Untested spreadsheets are riddled with errors.
Miller (2005)
The quality and reliability of spreadsheets is known to be poor.
Bishop & McDaid (2007)
Spreadsheets are more fault-prone than other software.
Kulesz & Ostberg (2013)
The issue is not whether there is an error but how many errors there are and how serious they are.
Panko (2007)
Spreadsheets are commonly used and commonly flawed.
Caulkins, Morrison, & Weidemann (2008)
94% of the 88 spreadsheets audited in 7 studies have contained errors.
Panko (2008)
Never assume a spreadsheet is right, even your own.
Raffensperger (2001)
Spreadsheets are easy to use and very hard to check.
Chen & Chan (2000)
It is irrational to expect large error-free spreadsheets.
Panko (2013)
The results given by spreadsheets are often just wrong.
Sajaniemi (1998)
Spreadsheet errors... a great, often unrecognised, risk to corporate decision making & financial integrity.
Chadwick (2002)
Errors in spreadsheets are as ubiquitous as spreadsheets themselves.
Colbenz (2005)
Most large spreadsheets have dozens or even hundreds of errors.
Panko & Ordway (2005)
Spreadsheets are often hard, if not impossible, to understand.
Mireault & Gresham (2015)
Your spreadsheets may be disasters in the making.
Caulkins, Morrison, & Weidemann (2006)
Overconfidence is one of the most substantial causes of spreadsheet errors.
Sakal, et al (2015)
The software that end users are creating... is riddled with errors.
Burnett & Myers (2014)
60% of large companies feel 'Spreadsheet Hell' describes their reliance on spreadsheets.
Murphy (2007)
Spreadsheets contain errors at an alarmingly high rate.
Abraham, et al (2005)
Programmers exhibit unwarranted confidence in the correctness of their spreadsheets.
Krishna, et al (2001)
Spreadsheets... pose a greater threat to your business than almost anything you can imagine.
Howard (2005)
Spreadsheets are dangerous to their authors and others.
Durusau & Hunting (2015)
Even obvious, elementary errors in very simple, clearly documented spreadsheets are... difficult to find.
Galletta, et al (1993)
Every study that has looked for errors has found them... in considerable abundance.
Panko & Halverson (1996)
Spreadsheet errors are still the rule rather than the exception.
Nixon & O'Hara (2010)
Research on spreadsheet errors is substantial, compelling, and unanimous.
Panko (2015)
Spreadsheets are the most popular live programming environments, but they are also notoriously fault-prone.
Hermans & van der Storm (2015)
Spreadsheet errors are pervasive, stubborn, ubiquitous and complex.
Irons (2003)
The untested spreadsheet is as dangerous and untrustworthy as an untested program.
Price (2006)
...few incidents of spreadsheet errors are made public and these are usually not revealed by choice.
Kruck & Sheetz (2001)
People tend to believe their spreadsheets are more accurate than they really are.
Caulkins, Morrison, & Weidemann (2006)
Spreadsheets are notoriously error-prone.
Cunha, et al (2011)
Every study, without exception, has found error rates much higher than organizations would wish to tolerate.
Panko (1999)
Studies have shown that there is a high incidence of errors in spreadsheets.
Csernoch & Biro (2013)
Spreadsheet development must embrace extensive testing in order to be taken seriously as a profession.
Bock (2016)
It is now widely accepted that errors in spreadsheets are both common and potentially dangerous.
Nixon & O'Hara (2010)
Despite overwhelming and unanimous evidence... companies have continued to ignore spreadsheet error risks.
Panko (2014)
Spreadsheets have a notoriously high number of faults.
Rust, et al (2006)
Errors in spreadsheets... result in incorrect decisions being made and significant losses incurred.
Beaman, et al (2005)
Spreadsheets are alarmingly error-prone to write.
Paine (2001)
Spreadsheets are extraordinarily and unacceptably prone to error.
Dunn (2010)
A significant proportion of spreadsheets have severe quality problems.
Ayalew (2007)
Developing an error-free spreadsheet has been a problem since the beginning of end-user computing.
Mireault (2015)
Despite being staggeringly error prone, spreadsheets are a highly flexible programming environment.
Abreu, et al (2015)

FizzBuzz in Excel: Why spreadsheet documentation is important

FizzBuzz is a simple word game that is sometimes used as a test for programmers. Here we use FizzBuzz to illustrate the importance of documenting your spreadsheet.

Summary of key points:
  • There are often many ways to write a spreadsheet formula.
  • Documentation is a very important, though often neglected, part of every spreadsheet.
  • Including documentation is important because it makes a spreadsheet easier to understand and use.
  • Types of documentation include cell comments, an 'About' worksheet, and text boxes.
  • Cell comments can be especially useful for describing how a formula works and pointing out subtleties that might otherwise not be obvious to the user.
  • Ensure that the documentation is updated as the spreadsheet changes.

Documenting spreadsheets

Do you document your spreadsheets? That is, do you include an 'About' worksheet, text boxes to provide information, and cell comments to describe what formulae do and how they work?

Documentation is an essential part of every best practice spreadsheet. Even a small amount of good documentation can make a spreadsheet much easier to understand and use, reducing both frustration and errors. In this article we use a simple task to demonstrate the use of cell comments to describe the subtleties and tricks used in some formulae.

The FizzBuzz function

FizzBuzz requires implementing a simple function. In this spreadsheet version, the task is to write a formula, in rows 1 to 100 of a column, that returns:

  • "Fizz" if the row number is divisible by 3.
  • "Buzz" if the row number is divisible by 5.
  • "FizzBuzz" if the row number is divisible by both 3 and 5.
  • The row number in all other cases.

Before proceeding, you might like to have a go at implementing the FizzBuzz function yourself. Then come back and compare your method with our methods in the next sections.

Alternative methods to implement FizzBuzz

There are many ways in which the FizzBuzz function could be implemented in a variety of programming languages. Here we present four alternative methods using spreadsheet formulae.

The methods we implement are:

  • Method 1: Literal interpretation, using a helper column.
  • Method 2: Literal interpretation, using the ROW() function.
  • Method 3: A more compact version that inverts the logic.
  • Method 4: An even more compact version, using the CHOOSE function.

Note that the spreadsheet used in this article is available to download.

Method 1: Literal interpretation, using a helper column

Our first method uses nested IF functions and a helper column, as shown below. This is essentially a literal interpretation of the FizzBuzz function. It is fairly straightforward, so it doesn't require much documentation.

There is one subtlety that may not be immediately apparent. If we evaluate the cases in the order specified in the function specification above, ie. starting with "divisible by 3", then the formula would fail for the "divisible by both 3 and 5" case.

This happens because subsequent nested IF statements are ignored once a TRUE result is found, so we need to evaluate "divisible by both 3 and 5" first. To see why, try changing the formula to do "divisible by 3" first.

It is important to document this subtlety, to ensure that the user doesn't alter the formula without understanding why the evaluations are done in that order.

Method 1: Literal interpretation, using a helper column
Method 1
This method refers to the helper column F to tell the formula which row it is on. The cell comment documents how the formula works, including pointing out a subtlety about the order in which the cases are evaluated.

Method 2: Literal interpretation, using the ROW() function

Our second method, shown below, is very similar to the first. The key difference is that it uses the ROW() function to return the row number of the current cell. This enables the same formula to be used in all cells, without needing a helper column.

Again, we use a cell comment to describe how the formula works.

Method 2: Literal interpretation, using the ROW() function
Method 2
This method dispenses with the helper column, using the ROW() function instead. Otherwise it is very similar to Method 1.

Method 3: A more compact version that inverts the logic

Our third method, shown below, looks to shorten the formula used in Method 2. It does this by inverting the logic of the IF functions. That is, instead of testing "If divisible by ...", it effectively tests "If not divisible by ...".

This formula is deceptively simple. It works because the IF function interprets a value of zero as being equivalent to the Boolean value of FALSE and any other value as TRUE. The MOD function returns zero (FALSE) only when the divisor test is TRUE. This is the opposite of what we would normally expect, so the return value is in the "else" clause of the IF function rather than in the "then" clause.

It would be very easy to misinterpret this formula, given its inverted logic, therefore including some documentation is very important.

Method 3: A more compact version that inverts the logic
Method 3
This method inverts the logic of Method 2 to produce a shorter formula. When relying on a trick like this it is very important that we document how the formula works, to reduce the likelihood of misunderstanding.

Method 4: An even more compact version, using the CHOOSE function

Our final method, shown below, uses a CHOOSE function to make the formula even more compact.

In a sense, this method is a more natural approach than using multiple IF functions. The task is to choose one of several options, given a specific criterion. That is exactly what the CHOOSE function does.

The tricky part is that calculating the criterion is fairly complicated and subtle. In this situation, it is essential that we document how the formula works. Future users of the spreadsheet - including ourselves - should not be left to ponder how this mysterious formula gets the right answer.

Method 4: An even more compact version, using the CHOOSE function
Method 4
This method requires significant explanation, rather than leaving the spreadsheet's users to work it out for themselves.

Conclusion

Documentation is an important, though often neglected, part of every spreadsheet:

  • Use cell comments to explain how formulae work.
  • Beyond cell comments, also include other types of documentation - such as an 'About' worksheet that describes the workbook, and text boxes for more extensive description, instructions, or explanation.
  • Ensure that the documentation is updated as the spreadsheet changes.

Downloads

Download the spreadsheet used in this article:
Fizz Buzz
Note: To see the cell comments in Excel, you may need to enable editing.

Comments

If you have any comments about this article, then please contact us.

Go to top