The Floating Point Number Problem

[FrederikE]

When working with Alteryx, you might have encountered a certain issue where numbers are not calculated the way you want them to; instead, they are a little bit off. For example, you have an Excel sheet with the value “0.37”, but in Alteryx, it shows “0.369999945...”. Meaning that your value is a tiny bit smaller than it should be.

This difference isn’t a bug of Alteryx. So why is it happening?

When you type in a number like 0.37 into your computer, it must be saved somehow. A computer can only store two different states, 0 and 1. Therefore your computer must transform the number into a format that uses only zeros and ones. Such a number is called a binary number.

Decimal Numbers vs. Binary Numbers

Decimal numbers are the ones we normally use in our everyday lives. They have a very simple logic behind them which makes it easy for us humans to read and calculate with them.

Let’s look at how the number is stored in your memory with an example. Let’s take the number 317 – in the decimal system, each digit is multiplied by 10^x where x represents the position (right to left) of the digit in the number. Therefore 317 can be divided into 3*100+1*10+7*1.

In the binary system, only two different digits (0,1) are used instead of 10 different ones (0-9) in the decimal system. To output the same value of 317, each position adds a different value to it. Every digit contributes only 2^x to the total number instead of the 10^x within the decimal system. This then transforms our 317 (decimal) to 100111101 (binary). The value of the number is the same, just the logic behind the number is different.

This is essentially what the computer has to do with every single number we type in because it only can store the values with a 0 or 1 (one bit).

Decimal Places in the Binary System

This example, of course, only used an integer value – if we look at a decimal number, it gets more complicated. The values behind the decimal separator are then multiplied by 10^-x, with x being the position from left to right.

So, let’s look at 0.37. With the decimal system, this is pretty simple. With the binary system, we need many digits.

If we would only save 5 decimal places in the binary format, we would have 0.01011, which means 0.25+0.0625+0.03125, which is  0.34375 and, therefore, a bit too low.

Hence, we need more digits. If we would take 10 decimal places, we end up with 0.36914…

We see that additional decimal places help us to get closer to the real number of 0.37, but we need a lot of storage space for each number if we want to be extremely precise with it. Many numbers could never be shown completely as they end up with an infinite amount of decimal places. This is similar when you try to show 1/3 in the decimal system (0.3, 0.33, 0.333, etc.). 

In Alteryx, we can tell the computer how much storage should be used to store such a number. When talking about decimals, we can select to store them as a “Float” or a “Double” - this determines how many bits the computer uses to store the number. With float, we do have 4 bytes, which is 32 bits (binary number with 32 digits) and leads to a precision with 7 digits. A double uses 8 bytes instead and, therefore, 64 bits and a precision of 15 digits.

Now you might wonder what happens in Alteryx when you round the number of our example.

Alteryx is not able to round this number to 0.37 because it cannot be stored (at least not as a float). So, what happens here is that it is just displayed differently – the number saved in your memory will still be the same as before, as there is no other option to do it.

In most cases, the Floating Point Number Problem will not significantly affect the results of your workflow, especially after rounding. However, it shows quite well how our computers can have major problems with seemingly easy tasks as they also underly physical limitations.