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

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.

## 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 1: Literal interpretation, using a helper column 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 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 2: Literal interpretation, using the ROW function 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 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 3: A more compact version that inverts the logic 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 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.

Method 4: An even more compact version, using the CHOOSE function 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.