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 good spreadsheet. Even a small amount of 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
ROWfunction. - Method 3: A more compact version that inverts the logic.
- Method 4: An even more compact version, using the
CHOOSEfunction.
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. This is essentially a literal interpretation of the FizzBuzz function. It is fairly straightforward, so it doesn't require much documentation.
G1: =IF(AND(MOD(F1,3)=0,MOD(F1,5)=0),"FizzBuzz",IF(MOD(F1,3)=0,"Fizz",IF(MOD(F1,5)=0,"Buzz",F1)))
There is one subtlety that may not be immediately apparent. If we evaluate the cases in the order specified in the function specification above, i.e. 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 2: Literal interpretation, using the ROW function
Our second method 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.
H1: =IF(AND(MOD(ROW(),3)=0,MOD(ROW(),5)=0),"FizzBuzz",IF(MOD(ROW(),3)=0,"Fizz", IF(MOD(ROW(),5)=0,"Buzz",ROW())))
Again, we use a cell comment to describe how the formula works.
Method 3: A more compact version that inverts the logic
Our third method 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 ...".
I1: =IF(MOD(ROW(),15),IF(MOD(ROW(),5),IF(MOD(ROW(),3),ROW(),"Fizz"),"Buzz"),"FizzBuzz")
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 4: An even more compact version, using the CHOOSE function
Our final method uses a CHOOSE function to make the formula even more compact.
J1: =CHOOSE(1+(MOD(ROW(),3)=0)+2*(MOD(ROW(),5)=0),ROW(),"Fizz","Buzz","FizzBuzz")
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.
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
Comments
If you have any comments about this article, then please contact us.
