Excel Formula – 7 Critical Reasons to Avoid Hard Coded Numbers

Have you said: this Excel Formula is just for this occasion? You know there is nothing more false than such affirmation because an Excel Spreadsheet sooner or later snowballs.

Your ad-hoc Workbook goes out of your tenure very easily: it is emailed, copied, revamped, etc. Treating Excel as a scratchpad is an underestimated thread.

Not aware yet?

Know what happened to this big guy due to filling Excel Models with Ad hoc formulas…

NASA misstated by $644M: undetected spreadsheet errors in GAO report number GAO-04-754T entitled ‘National Aeronautics and Space Administration: Significant Actions Needed to Address Long-standing Financial Management Problems’…

‘NASA’s fiscal year 1999 SBR was misstated by a reported $644 million due, in part, to a misinterpretation of guidance and errors in NASA’s ad hoc process for generating budgetary information. As a result, the NASA locations used various methods to extract the data for this line item from their separate systems and entered the data on spreadsheets, which were then compiled by NASA headquarters’

Spreadsheet mistakes – news stories collated by the European Spreadsheet Risks Interest Group. They illustrate common problems and errors that occur with the uncontrolled use of spreadsheets, with comments on the risk and possible avoidance action.

You will Get Wrong Results Because you may Forget to Update the Formula after a Change in a Value/ParameterHere you are presented with 7 Best Practice Tips to not use hard coded numbers in your Excel Formulas…

Imagine the following Compound Interest Rate Formula:

=B2*((1+0.055)^B3)

Where

B2 = amount

B3 = number of years

B4= Interest Rate (5.5% hard coded in formula)

Well, now think this:

You change amount in B2 and Interest Rate in B4 (to 6.5% for example). The output will reflect the change in amount but not in interest rate (the formula remains the same 5.5% version).

1) You may Spend Time by Manually Updating the Hard-Coded Number in all Instances of Ad Hoc Formula

Imagine you need to change the value from 5.5% to 6.5% in ten instances of the formula spread in the Excel Workbook.

Well…

You have a practical solution: Search and Replace Command but you will be prone to errors and the perceived accuracy may be affected too.

2) You will Sooner or Later Lose the Track of all Instances of an Ad Hoc Excel Formula

Excel Workbook snowballs; so, once the formula has passed from fellows to fellows or clients you can expect any catastrophe.

3) Errors Increase its Magnitude as Formula may Go Nested and Nested in Complex Mathematical Relationships

  • The formula =B2*((1+0.055)^B3) is nested in other calculations that can be later be used in other complex model
  • The model output is used as input for other spreadsheets (it can be sent by email as plain number); once there, the number can be used as main data for analysis (the product of such model may be also sent by email)

The degree of error is simply unpredictable; for the purpose of illustration, imagine these two cases:

4) Difficulty to Detect Errors Produced by Hard Coded Numbers

  • Double relationship: a hard-coded-formula feed a formula while the same hard-coded-formula with an updated value feed another one
  • Oldest and newest versions of the hard-coded-formula may go together in a same spreadsheet without being detected

You will face those errors difficult to confirm even when they are found. This could be the case of:

5) Hard-to-Debug Formula Because it is not Traceable with Debugging Tools

Tracing precedents/dependents commands simply could not be used because the values are not linked to any Excel Cell.

6) You may Lose Time, Money and Credibility

These kinds of errors could be very costly as report NASA above. At that level, these problems appear due to lack of regulations of Spreadsheet use at the corporate level; the fact is that if you are the responsible of such blast, your credibility and job may be affected.