Never Use Fixed Numbers In Formulas
We have all seen “magic numbers” like this = SUM(A4:A12)*0.145. However, the use of magic numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.
Confusing for Future User
While creating a formula, the creator knows exactly what a fixed number represents, however, these things are hard to remember, and without good documentation, they will probably forget the purpose of a fixed number. When you are uncertain of the origin of a number, it is hard to know whether or not it needs to be changed.
Overlooked in Times of Changes
Consider the VAT percentage, when the number 19 is used to represent VAT in formulas everywhere, and the VAT is changed from 19 to 21 (which happened in the Netherlands in 2010) then you have a problem. Changing all fixed 19’s to 21’s is a lot of work and search and replace can be dangerous! We know of a Dutch car dealer that suddenly had 21 cars in his inventory when the tax added value rate changed from 19 to 21 in 2010.
Creating a separate cell containing the number 19 is a lot better. Label it with VAT and reference it everywhere. When the VAT changes again, this cell is the only one that will need to be updated.
Risk of Typos
Thirdly, when a magic number is tucked away deep in a formula, it is hard to check, because it is hidden within the formula. When the number is placed in a separate cell it is easy to see which values are being used. A zero too many or too few is more common than you think! It’s simply not worth the risk to put fixed numbers in formulas because mistakes are all too common, and making this mistakes harder to spot doesn’t help anyone.
There Are Exceptions
Of course, there are exceptions. The numbers 1, 2, 10 and 12 occur so frequently that they often do not need to be separated out and labeled. Labeling the number 12 with “number of months” is a good example of taking this principle too far. We highly doubt that years will ever start to last 13 months, and if they do we will have bigger things to worry about than spreadsheet maintenance. As with any principle just use good logic, and think about the next person who will open this spreadsheet, make things simple and easy to understand for them.
Conclusion: Do Not Use Fixed Numbers
Be disciplined, even when it is tempting. After all, it is easy, it is quick, and maybe you are thinking “just this once,” but just do not use those fixed magic numbers. It just is not worth the risk to make your work ambiguous and hard to maintain.
If you agree with us, then you might be worried about fixed numbers already lodged in your work! Well we have the solution, you can use our Excel add-in, PerfectXL, to easily check for fixed numbers. We generate an overview of all the fixed numbers in your spreadsheet as well as their locations. Create a free account now!
The image shows the PerfectXL overview of fixed numbers in a spreadsheet, as one type of risk in the category ‘Calculation Doubts’