Constants can change, this is the reason why you need to manage fixed quantities efficiently in one place. Remember that Excel Formulas with constants are used all through sheets so updating hard coded quantities manually means a waste of time and a weak trust in your own model.
Here are some examples:
- An incentive for employees has been 5.1% for years. You assume this is a constant but new politics could change it to 5.5% for example
- Newton Gravity with two decimals 6.67 m3/kg/s2 could be later changed to a greater precision: 6.67428 m3/kg/s2
- A change in the constant is reflected throughout the model during a sensitivity analysis
Hopefully, you can store constants in a central location with Named Ranges in Excel. Here’s how…
1) Open the New Name Dialog (Excel 2007)
Go to Formula>Defined names>Define name.
For Excel 2003 users, proceed this way…Go to Insert>Name>Define
Become aware of…
“Scope” and “Comment” options are not available in Excel 2003 (see graphic 1)
2) Create the Constant: Define Name, Scope and Description
- Specify the Name (without spaces)
- Set the Scope: Workbook or on a Sheet basis
- Specify the value of the constant with “Refers to:”
This is easily done in 3 steps
You can add comments to your constant and get oriented during the inserting process. The new Formula Autocomplete is a big deal. See graphic 2…
3) Insert the Constant in Your Excel Formulas
Use new Formula Autocomplete tool to do the job very easily and intuitively. You can use F3 too.