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 originates from. And fixed numbers don’t change when needed, and thus might be overlooked when a change is made.
Confusing for Future User
While creating a formula, the creator of course knows what is meant with the fixed number. However, at a later time this is very hard to know. When you are uncertain of the origin of a number, it is hard to know whether or not to change it.
Overlooked in Times of Changes
Consider the VAT percentage. When the number 19 is used in formulas everywhere, and the VAT is changed from 19 to 21 (which happened in the Netherlands in 2010) changing all fixed 19’s to 21’s is a lot of work. And search and replace can be dangerous! We know a story 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 easier. Label it with VAT and link to it everywhere. When the VAT changes again, this cell is the only one that will need updating.
Risk of Typos
Thirdly, when a magic number is hidden in a formula, it is hard to find a typo, because the formula (and with it the number) is hidden. A zero too many or too few is more common than you think!
There Are Exceptions
Of course, there are exceptions. The numbers 1, 2, 10 en 12 occur so frequently that they do not need their own meaning. Labeling the number 12 with “number of months” is taking it a little bit too far. We do not think that years will ever get 13 months.
Conclusion: Do Not Use Fixed Numbers
Be disciplined. Even when it is tempting to do (easy, quick, only for this one time): do not use those fixed magic numbers. Are you worried about magic in your sheets now? You can use PerfectXL to easily check all fixed numbers. We generate an overview of all of them and 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’