By J.D. (Jim) Whiteside, II, PE

Here is something that may help when calculations are experiencing “rounding errors”, or when calculation totals of millions of dollars are off by $100,000. When calculations are off, a lot of time is wasted reviewing spreadsheets to find a possible error in some cell math. From experience, the solution is never found after hours of work. Being defeated and facing a presentation deadline, the solution is to manually edit the presentation with the correct answer and move onto the next assignment. But this means the problem was not solved and it will repeat itself in the future. 

The problem is in the declaration of the wrong calculation variable(s) being used in calculations. Generally, a cell in Excel can be trusted to handle a floating-point number. Most folks errantly assume that a floating-point number is highly precise. 

The range limits of 3 common variable used in spreadsheets and software is as follows:

Single Range Limits: 

-3.402823E38 to -1.401298E-45 for negative values

1.401298E-45 to 3.402823E+38 for positive values.

Double Range Limits:

-1.79769313486232E+308 to -4.94065645841247E-324 for negative numbers

4.94065645841247E-324 to 1.79769313486232E+308 to positive numbers up to 14 decimal places

Decimal Range Limits: Can hold values up to 28 digits 

79,228,162,514,264,337,593,543,950,335 is the upper limit if no decimals are used

+/- 7.9228162514264337593543950335 If decimals are used.

Selecting a variable on the range limits is not sufficient. For example, multiplying a number by 10^n, where n is positive, you get the expected result. e.g., 2.3 * 10^1 = 23 Even a variable declared as a SINGLE is sufficient. It is not common knowledge that these three common variable declarations are handled differently by computers.

However: Assume that the variable needs to be more precise, therefore, the variable is declared as DOUBLE. If a number is declared as DOUBLE and the variable is multiplied by 10^n where n is negative it results in an approximation precision error. e.g., 0.23 * 10^-1 = 2.299999999999…. Notice that the error is in the 10th decimal place. The correct answer is 2.3. The error in the next calculation could be significant. For example, 0.2 * 1 million is 200,000 rather than 0.3 times 1 million = 300,000. The answer will be short by 100,000. 

Approximation precision error is caused by computers evaluating numbers. Computers use routines that use numerical series (like Taylor, Maxwell, etc.) to approximate values. Computers have limits and therefore they use truncated values like for e, PI, and in this example 1/10 (10^-1). 

For a lot of everyday work with spreadsheets, it may not matter, and the error may never be noticed. But this lack of precision is causing errors in my work. It is causing disruptions to risk analysis where being slightly off is compounded like compound interest on a savings account. While compound interest on a savings account is good, compound error on something like a risk analysis is really bad.

In order to minimize compounding math errors caused by approximation precision error variables need to be declared as DECIMAL. DECIMAL variables have significantly reduced approximation errors. The difference between DOUBLE and DECIMAL is significant. Revising the previous calculation example demonstrates this point:   0.23 * 10^-1 = 2.3.  A DECIMAL variable will result in significantly less error compounding.

Error compounding shows up in performing risk analysis when you have variables declared as DOUBLE and you are performing 10’s of thousands of iterations on hundreds of activities in something like a schedule risk analysis (SRA). It is going to produce a result that may be skewed the wrong direction. Rather than having a “high” probability, the result may actually be a “low” probability. The analysis might have guided management into the wrong decision. 

For critical work or work with lots of calculations, base variable declarations as DECIMAL. A DECIMAL declaration is equivalent to the old Fortran/COBOL era use of DOUBLE PRECISION. However, while DECIMAL is superior to DOUBLE, critical calculations use a DOUBLE PRECISION FLOATING-POINT (FP64). FP64 means the number can have 64 digits. A DOUBLE is limited to 14 digits. All programs used for financial transactions use the FP64 declarations for all the variables in the calculation path. FP64 isn’t available on general consumer software. In fact, consumer computing platforms can’t handle FP64 because they simply do not have the necessary memory or processing power to handle FP64 variables.

I need to find a used CRAY somewhere. Until then, I am now using DECIMAL declarations.