Search | Hardwired constants

This command selects all formula cells within the current selection that contain a numeric element.  Formula cells that contain numbers only as part of text strings, function names, and name labels are not selected by this command.

The option is given to ignore certain numeric elements as determined by the user.  By default, numeric elements that have the values 0 and 1 are ignored as these are required in many Excel functions.

Why would you want to use it?

Good practice in developing models dictates that all input values should be clearly stated on the face of a worksheet rather than buried within formulas.  This command allows you to locate any cells that contain buried numeric values; in many cases, these numeric values should be split out onto the face of the worksheets.

An example of such a formula is =A1/365.  It embeds in the formula the assumption that all years have 365 days.  In some cases, the extra day in a leap year may make this assumption wrong by a material amount.

What is dangerous about hardwired constants is that they may escape checking.  A common step in building or reviewing a model is to cross-check the numerical assumptions used against any documentation provided.  Both Excel and OAK provides mechanisms for identifying the numerical assumptions.

Hardwired constants, on the other hand, will be not appear in either Excel's selection or OAK's listing, as they are embedded in formulas.  They may escape checking.  The Search selection | Hardwired Constants command in OAK  provides a method by which these hardwired constants in formulas may be found and checked.

How to use it

Select a range and choose the command from the menu; a dialog is displayed that gives you the option not to treat certain numeric elements as hardwired constants.  The command will either select those formula cells within the current selection that contain a buried numeric input or return a “No hard wired constants found in selection” message.

Wrinkles

Many Excel functions require user specified numeric parameters that cannot really be considered to be model inputs.  An example of this is the MATCH function where the third parameter specifies the match type.  Cells containing functions of this type are typically selected by this command even though they are not strictly hardwired numeric inputs.  The majority of these cells can be avoided by opting not to treat 0 and 1 values as hardwired constants.

We recommend that you periodically opt to treat 0 and 1 values as hardwired constants as these numeric values can have a profound effect on the logic of a model and should be examined carefully (it is not suggested that these 0 and 1 values should be split out onto the face of the worksheets).

We also recommend in general that other numeric values are not excluded from being treated as hardwired constants before careful consideration and examination of the model.

What you could do if you didn't have OAK

There is no automatic way to detect hardwired constants inside formulas using Excel alone without OAK.