About the increased accuracy of calculations in Pages, Numbers, and Keynote

After updating Pages, Numbers, and Keynote, you might notice minor changes to the results of calculations in your documents. That’s because the latest version of iWork includes an update to the calculation engine, which produces results with even greater accuracy than before.

Most formulas will show no visible difference, but calculations that result in or use extremely small numbers may now show an even more accurate answer.

Example 1

In previous versions of Numbers, Pages, and Keynote, a calculation such as = 10.0 - 9.8 results in a value of 0.199999999999999.

Altering the number of decimals shown in the cell (between 1 and 14 decimal places) can remedy the issue to show the expected result of 0.2. Increasing the number of decimals shown in the cell displays 0.199999999999999000000000000000.

This happens because of the way binary stores decimal numbers internally. Binary representations of decimal numbers can induce very small discrepancies. The calculation engine in Pages, Numbers, and Keynote has always employed sophisticated techniques to keep these differences from aggregating within calculations, but at very high precisions the differences can become apparent.

The new calculation engine dramatically improves upon previous versions by using a decimal representation of numbers throughout the calculation.

The result is that the same calculation = 10.0 - 9.8 now results in a value of 0.2. Increasing the number of displayed decimals now shows a value of 0.2000000000000000000000000000000.

Example 2

In previous versions of Numbers, the formula ROUND(8.415,2) returns 8.41. This also happens because of the way values are stored internally in binary.

With the new calculation engine, ROUND(8.415,2) returns 8.42.

About Binary Coded Decimal versus Decimal Floating Point encoding

Most computers store numbers as a series of zeros and ones, which are binary values. A process called Binary Coded Decimal (BCD) encoding converts fractional numbers to their binary counterparts. When numbers are encoded using BCD, some values can't be represented with complete accuracy. For example, 0.2 in binary form 0.001100110011... (repeating). The value must be truncated in order to encode it using BCD, which slightly alters the stored value. Even though discrepancies introduced by BCD can be very small, these discrepancies become more apparent when amplified through certain types of calculation chains.

The latest versions of Pages, Numbers and Keynote encode numbers using 128-bit Decimal Floating Point (DFP) encoding. This is a much more accurate way to encode decimal fractions that avoids rounding errors typical of BCD encoding. In addition, the updated calculation engine operates directly with numbers encoded with DFP—values in table cells are stored more accurately, and formula results are computed more accurately.

Many formulas will show no visible difference, but calculations that result in or use extremely small numbers may now show an even more accurate answer.

Published Date: