Guidelines:
- Constants, symbols, and other special data values are in named ranges
- Scale factors are not embedded in formulae
Put special data values in a named range, then use that name in formulae.
Having a name gives the value a clearer meaning, making it easier to understand when used in formulae.
Define special values using named ranges
Rather than hard-coding special values in a formula, a better approach is to put the value in a named cell or block of cells. For example, rather than having =0.3*B7, make a named range called TaxRate, with the value 0.3, then use the formula =TaxRate*B7.
Naming special values has several advantages:
- All formulae that use the values have a single source.
- The values can be easily changed.
- We can label the values, defining what they mean.
- Units or other information can be added, to aid the user.
- Other documentation, such as a cell comment, can be added to supply further guidance for the user.
Avoid scaling factors embeddedd in formulae
A specific case of special values in formulae is the common practice of using scaling factors that convert between orders of magnitude.
For example, we have a dollar amount in B4 and in another cell =B4/1000 expresses the amount in units of $000. This is usually a bad idea, as it can lead to formulae that inadvertently work with values of different magnitudes. In addition, if we later want to change the scaling factor, say to units of $1,000,000, then doing so quickly and accurately can be difficult.
A better approach is to change the display of the values using a custom number format, like #,##0.0,;-#,##0.0,;- for multiples of $1,000. This changes only the display – the underlying values are unchanged.
But if you must scale the values, then put the scale factor in a named cell and refer to it. For example, put the scale factor of 1000 in a cell named Scale, then use a formula like =B4/Scale. This approach still has the risk of formulae working with numbers of different formulae – you'll need to manage that risk separately.
Don't use symbols directly in formulae
Sometimes we want to use a symbol to mark a value in some way. For example, using a tick or a cross to indicate if a result is good or bad. We can use Conditional Formatting for this purpose, which is usually a better method. But sometimes we want to use a formula, like:
=IF(B10<=$B$3,"✘","✔")
Instead of hard-coding the symbols in the formula – or potentially many formulae – put the symbols in cells and name them, for example: Bad and Good. Then the formula can be:
=IF(B10<=$B$3,Bad,Good)
This approach makes the meaning of the symbols clearer. Also, if we want to change the symbol, then we need to change only one cell instead of every place where the symbol is used.
Exceptions for obvious special values
Exceptions can be made for obvious special values, such as 0 and 1, or other values where the meaning is clear. For example, we can convert a number stored as text to be an actual number by multiplying by 1, like =1*A3. We don't need to put the 1 in a separate cell.
However, be sure that the meaning is clear to other users, not just to you. It might be obvious to you that the formula =D5*3.6 makes use of the fact that 1 kWh is equivalent to 3.6 MJ. But other users of your spreadsheet may not immediately recognize the meaning of that 3.6 number in a formula, so make it clear for them by putting it in a named cell with helpful labels.
