Tag: floating point

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.

Domain-aligned bugs

Frank C. Müller [CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0) ]
Imagine that you are an user of a typical enterprise software that handles commercial products and their prices. There are different prices in the software that are somehow related to each other. There is the purchase price that indicates your cost if you buy the product. There is the retail price that gets listed in your price lists and is paid by your customers, should they buy the product. You probably already figured out that the retail price should never be lower than the purchase price, because that would mean you lose money with every successful sale.

Let’s say that the enterprise software not only handles products, but also parts. Several parts combined, with some manufacturing effort, result in a product. Each part has a purchase price, the resulting product has a retail price. The retail price of the product should be higher than the sum of purchase prices of the parts. If it isn’t, you lose the costs of the manufacturing effort and some extra money with every successful sale.

If for any reason you cannot clearly estimate your manufacturing effort, the enterprise software has another input field for an amount of money that you can add to the sum of the parts’ costs. We call this field the “sales bonus”. So, if you sell a product made up of parts, your customer has to pay a price that consists at least of the retail prices of the parts and the sales bonus. Of course, your customer has an individual discount percentage that needs to be subtracted from the total price. Are you still following?

You are now thinking in the domain of price determination and financial mathematics. If you were the user of said enterprise software, you’d probably expect some bugs like these:

  • It is possible to enter a retail price lower than the purchase price
  • The price of products manufactured from parts isn’t calculated correctly
  • It is possible to enter a negative sales bonus
  • The total price with discount could be lower than the sum of purchase prices of the parts without a warning

All of them are bugs in the domain. All of them can be explained to a domain expert or a user with terms and concepts from the domain.

But what about the bug when you sell a product that consists of three parts, each with a retail price of 10 €, and a sales bonus of 5 €. You want to create a quote for your customer and the price shows up as 34,99999999998 €. You are a bit bewildered and try to countervail the apparent rounding error by changing the sales bonus to 5,00000000002 €. After this change you get another crazy total price and your prices in the database are different from what you entered, too. Everything seems to destabilize and deviate further and further from clear cut prices.

As a programmer, you know what happened. You know what caused this effect of numerical instability. Somebody stored monetary values in a floating point number. You know that is a bad idea and you’d never do this. But this blog post isn’t about you or what you should do or not do. It is about the user, expert in his domain, that stumbles over the bug as described and has to make some decision on how to fix it. This user cannot use any knowledge from the domain to even understand the mechanics of the bug. You, as the programmer, cannot explain this bug in terms of things the user already knows. You need to be vague (“the software doesn’t store the exact values, just approximations”) or introduce additional complexity (“we store this value by splitting it into a significand and multiply it with a factor consisting of a fixed base and an exponent. We can omit the base and just store the significand and the exponent and express a very large numerical range in just a few bits. Think about how cool that is!”).

Read the last explanation again, from the viewpoint of a salesman. We want to add some prices in the range of a few €, slap a moderate discount on top and call it a day. We don’t care about bits or exponential formulas. That is not part of our domain and it shouldn’t affect our domain or software that works in our domain. Confronting us with technical details reflects negatively on your ability to solve our problems. You seem to burden us with your problems in exchange.

As domain experts, we want only domain-aligned bugs.