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.

Excel Guideline 02: Never Use Fixed Numbers in Formulas :: PerfectXL

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!

Never Use Fixed Numbers in Formulas - Risks :: PerfectXL

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!

Never Use Fixed Numbers in Formulas - Fixed Numbers Overview in PerfectXL

The image shows the PerfectXL overview of fixed numbers in a spreadsheet, as one type of risk in the category ‘Calculation Doubts’

Never miss a Guideline!