Real Numbers in 4th Dimension

By Gordon Muirhead, ACI Technical Support

Technical Note 97-4

Technical Notes for 97-01-02-January/February 1997

If your 4th Dimension database uses decimal numbers, you probably store and manipulate those numbers in fields and variables of type Real. This technical note examines the characteristics and behaviors of Reals in 4D, and touches on the following issues:

How are real numbers stored?

NAN's: When is a number not a number?

Using 4D Customizer to change the way real numbers display

Comparing real numbers

Although real numbers are displayed as decimal (base 10) numbers, they are stored in 4th Dimension fields and variables as binary floating point numbers. On the Macintosh® PPC and Windows platforms, reals are operated on in memory in 64 bits (8 bytes). The 64-bit format can accommodate 15 decimal digits in its 52-bit significand. The format is:

Where:

s = sign (+ or -) (1 bit)

e = exponent (the location of the decimal point) (11 bits)

m = the mantissa, or significand (the significant digits that make up the actual number) (52 bits)

For example, -1.234e+25 is a very large negative number. It breaks down like this:

Sign: - (negative)

Exponent: +25 (i.e., 10^25)

Significand: 1.234

Note that the most significant bits of both the exponent and the significand are on the left, and the least significant bits are on the right.

On the 68K Macintosh a similar format is used, but the number is stored in 80 bits (10 bytes), and can accommodate 19 decimal digits in its significand.

Real number fields in 4th Dimension are always stored in the longer, 10-byte, 19-digit format, so that any database can be used on any platform. As numbers created on the PPC and Windows only have 15 significant decimal digits, and as they are stored in the 19-digit format, their last 4 digits are essentially random. Knowing this is important in understanding the "how" and "why" of the 4D Customizer Plus settings described in this technical note.

The range of real numbers on all platforms is approximately +/-1.8 X 10^(+/-308), or roughly the number 2 preceded by a decimal point and 307 zeros, to the number 2 followed by 308 zeros.

Real numbers are converted to binary floating point format for storage and calculation, and they are converted from this format for display. Real numbers are converted to and from a binary format, and all calculations are done in floating point binary format, therefore the values of reals are only approximate—there is chance for imprecision in the least significant digits of the significand. That's why it is extremely important that you remember to always round the results of calculations with real numbers.

Suppose you are calculating the total dollar amount on an invoice. Your code might look something like:

   [Invoice]SubtotalAmt:=Round(Sum([InvoiceLine]TotalAmt);2)
   [Invoice]Tax:=Round(([Invoice]SubtotalAmt*TaxRate);2)
   [Invoice]TotalAmt:=([Invoice]SubtotalAmt+[Invoice]Tax)

It is possible that the number you store in a Real may not be a real number at all. For example: 1 divided by 0 equals infinity. Another example: the square root of -1 is not a real number; it is an imaginary number, and is stored as a special bit pattern called a NAN (Not A Number).

NAN's can creep into your database as a result of invalid calculations, like taking the square root of -1 or dividing by zero. NAN's will be not equal to zero, not greater than zero, and not less than zero. They print as null strings.

NAN's propagate through arithmetic operations—if you add up a column of figures, and one of them is a NAN, the result is a NAN. If you have a report where totals just aren't printing or displaying, you may have one or more NAN's in your database.

You can find NAN's with:

                SEARCH BY FORMULA(String([File]RealFieldName)="")

This is the Preferences window in Customizer Plus:

Note that we have circled the value of the "Real Precision (Power Macintosh and Windows)" parameter. To understand how this parameter works, let's return to our discussion of real numbers.

Real numbers are stored as binary floating point numbers, which have three components:

Sign (+ or -)

Exponent (the position of the decimal point: +/-e1022)

Significand (the significant digits) (sometimes called the mantissa)

The "real precision" in Customizer Plus refers to the significand. The accuracy of the numbers on the rightmost portion of the significand (i.e., the "least significant" digits) is greater on the 68k Macintosh than it is on the Power Macintosh and Windows.

On the Power Mac and Windows, the 64-bit binary format, when translated to decimal, can accommodate 15 significant digits. On the 68k Macintosh, the 80-bit format can accommodate 19 significant digits. As all real numbers are stored by 4D in the 19-digit format, the rightmost 4 digits on Power Mac and Windows are essentially random, and should be ignored. This is the reason for the Customizer Plus setting for Power Mac and Windows: it allows the automatic truncation of those rightmost (least significant) random digits when displaying real numbers.

The number you enter in "real precision" is the position of the digit that 4th Dimension will use to round and truncate the least significant digits of real numbers, starting from the least significant (19th, or rightmost) digit. You can find the digit like this:

Subtract the "real precision" value from 19, to get n.

Find the leftmost non-zero digit in your number.

Count n digits, moving from left to right.

You've found the rounding digit.

For example, let's take the number .001234567890123456, with "real precision" set to 6:

19 - 6 = 13

Ignore the leading zeros.

Starting with the leftmost 1, count 13 digits moving from left to right.

The second 3 is the rounding digit.

Now that you've found the rounding digit, here's what happens:

If the rounding digit is not 9 or 0, nothing happens. The number is displayed as-is.

If the rounding digit is 0, all digits to the right of it are dropped. The rounding digit and all contiguous 0's to the left of it are dropped.

If the rounding digit is 9, all digits to the right of it are dropped. The rounding digit and all contiguous 9's to the left of it are dropped. The absolute value of the remaining digits is incremented by one.

Note that all of this occurs on the significand, regardless of the value of the sign and the exponent. It has nothing to do with the location of the decimal point.

When you perform an arithmetic operation using two real numbers, the precision of the result is determined by the number of significant digits in the original operators, as well as the number of significant digits in the result. Remember, the least-significant digits (to the extreme right) may be random. Here is an example of two subtraction operations and their results as displayed by 4th Dimension, on Windows, with "real precision" set to 5 in Customizer:

With "real precision" set to 6, the numbers become:

Set "real precision" to 7, and both results round:

You must be careful as you move the rounding digit farther to the left. For example, with "real precision" set to 7, the number 9,999,999,999,999 automatically becomes 9,999,999,999,998.99. You should change "real precision" very carefully, and only after much experimentation. In most cases, the optimum setting is 5, which is the default.

Important Note: This setting only affects display. In all of the cases described here, you will get (40.00 - 39.995) = 0.005 returning TRUE.

After the "real precision" algorithms are used to eliminate imprecision in the least significant (rightmost) digits of the significand, the sign and exponent are applied. The result is a real number. Now that we know where the decimal point is, and whether the number is positive or negative, the number has a real value.

Now we can compare our number to other reals. There is just one more thing we need to understand, and that is the idea of "epsilon rounding" for comparison purposes. Here is how it works:

The 6th digit past the decimal point is the "epsilon" digit. Comparisons on real numbers are done up to and including this digit, and no farther.

For example, in 4D, the expression:

0.123456 = 0.123455 is FALSE, since the first 6 digits to the right of the decimal point are not the same, but:

0.1234561 = 0.1234569 is TRUE, since the first 6 digits to the right of the decimal point are the same.

When using 4th Dimension, remember these points about real numbers:

They are stored and calculated as binary floating point numbers. The translation to binary floating point and back again to real decimal carries with it some imprecision. Therefore, you should always round the results of calculations with real numbers.

Real fields and variables can, as a result of invalid calculations, contain values that are not numbers (infinites and NAN's). If you have a report that is not totaling, this may be the reason. We've shown in this technical note how to search for NAN's in your data.

You can use 4D Customizer to change the way real numbers display.

Real numbers in 4th Dimension compare only through the 6th digit after the decimal point.