Tag: excel

The Number You See Is Not Necessarily the Number You Get

There is a particular class of bugs that is easy to dismiss as a floating-point problem.

For example you have a value in Excel: 25604.88

You open the spreadsheet, and that is exactly what you see. You read the cell with a library, however, and suddenly you get something like:

25604.880000000001

At first this looks like a broken spreadsheet, a broken XML file, or a broken library.

It is none of those.

An .xlsx file is a ZIP archive containing XML files. If you inspect the worksheet XML, you may encounter a value along the lines of:

<c r="A1">
    <v>25604.880000000001</v>
</c>

This is not Excel suddenly deciding that the user entered a different number.

The important distinction is that Excel displays numbers according to formatting and its rules for presenting floating-point values. The underlying numeric representation is based on binary floating point. Microsoft documents Excel’s use of IEEE 754 floating-point representation and the resulting precision limitations.

This distinction is worth understanding because it can turn a seemingly harmless value into a surprisingly difficult integration bug.

Why can’t a computer just store 25604.88?

Because computers don’t normally store floating-point numbers as decimal fractions.

A typical double uses the IEEE 754 binary64 representation. Conceptually, the value is represented using a sign, an exponent and a significand. There are 64 bits available in total, with 53 bits of precision in the significand.

This works beautifully for binary fractions.

For example:

0.5   = 1/2       = 0.1b
0.25  = 1/4       = 0.01b
0.75  = 1/2 + 1/4 = 0.11b
0.125 = 1/8       = 0.001b

All of these can be represented exactly in binary.

How to convert with simple Operations:

0.625 * 2 = 1.25 => 1
0.25  * 2 = 0.5  => 0
0.5   * 2 = 1    => 1
=> 0.101b

But other fractions can not be represented by binary fractions exactly like 0.1.

0.1 * 2 = 0,2 => 0
0.2 * 2 = 0.4 => 0
0.4 * 2 = 0.8 => 0
0.8 * 2 = 1.6 => 1
0.6 * 2 = 1.2 => 1 
0.2 * 2 = 0.4 => 0 // REPEAT
0.4 * 2 = 0.8 => 0
0.8 * 2 = 1.6 => 1
0.6 * 2 = 1.2 => 1
=> 0.00011001100110011001100110011...b

It is a unending number, limited by memory space for storing the number. When translating back to decimal is won’t be exactly 0.1 again.

0.00011001100110011001100110011b = 0.09999999962747097015d

Or for our example with 25604.88 the decimals 88 will be convert like this:

0.88 * 2 = 1.76 => 1
0.76 * 2 = 1.52 => 1
0.52 * 2 = 1.04 => 1
0.04 * 2 = 0.08 => 0
0.08 * 2 = 0.16 => 0 
0.16 * 2 = 0.32 => 0 
0.32 * 2 = 0.64 => 0
0.64 * 2 = 1.28 => 1
0.28 * 2 = 0.56 => 0
0.56 * 2 = 1.12 => 1
0.12 * 2 = 0.24 => 0
0.24 * 2 = 0.48 => 0
0.48 * 2 = 0.96 => 0
0.96 * 2 = 1.92 => 1
0.92 * 2 = 1.84 => 1
0.84 * 2 = 1.68 => 1
0.68 * 2 = 1.36 => 1
0.36 * 2 = 0.72 => 0
0.72 * 2 = 1.44 => 1
0.44 * 2 = 0.88 => 0 // REPEAT
=> 0.11100001010001111010...b

What does this mean for programmers?

The important lesson is we need to know what we are dealing with.

When reading numbers from external sources such as Excel, JSON or a database, don’t assume that the value you see is exactly the value your program receives. Check the actual value and the type your library produces.

For many calculations, this is completely fine. But when exact decimal values matter — for example, with money, accounting or other business rules — don’t leave this to chance.

Use a representation that matches the requirement. For example use BigDecimal or BigFraction for exacter decimal values.