Never use hard coded numbers in Excel formulas
We have all seen “magic numbers” like this = SUM(A4:A12)*0.145. However, the use of hard coded numbers (0.145 in this case) is a bad idea. A future user will not know where it came from, and hard coded numbers don’t change automatically (thus might be overlooked when a change is made).
Easily overlooked
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 hard coded 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 hard coded 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: don’t use hard coded numbers in Excel formulas
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 hard coded magic numbers in your spreadsheets. It’s just not worth the risk to make your work ambiguous and hard to maintain.