09-07-2016 09:09 AM - edited 11-17-2021 07:30 AM
Floating point numbers in a computer have some properties that are, unfortunately, surprising to even us engineers used to working with algebra. The problems all come from the limited precision and range. A “floating point” number is represented by a certain number of bits of precision, and a certain number of bits of exponent. In all modern computers the exponent is a power of 2. That means, effectively, that 1 is represented as “1 x 2^0”, 2 is “1 x 2^1”, 0.5 is “1 x 2^-1”, and 0.25 is “1 x 2^-2”. That’s great, but what happens if you want to represent 0.7 in the computer? Ah, well, it does not work out evenly at all. Just as 1/3 comes out as a repeating 0.33333… when represented as a decimal, most numbers do not work out evenly when represented in binary. 0.7 is (1/2 + 1/4 + 1/16 + 1/128 + 1/256 + … ), or as a hexadecimal fraction: 0.B333333… Remember you don’t get an infinite number of bits. If you cut it off after 12 bits, 0.B33, it will be a little bit too low, it will be like 0.699951172, or 4.88 x 10^-5 too small. If you cut it off after 14 bits and you round to the nearest value, you’ll get 0.700012207, or 1.22 x 10^-5 too high. Each extra bit may get you closer, but you will never be exact.
The most common floating point types stored in databases are typically called “float” and “double”, with a variety of different aliases. A “float” is typically a 32-bit quantity, with about 24 bits of precision, and 8 bits for the exponent. If you want to represent 4095.7 as a float, you will use 12 bits for the integer part, and you will have 12 bits left for the fraction part, and you will get 4095.699951172, just a little too low. On the other hand, if you want to represent 1023.7, you will use 10 bits for the integer part, and you will have 14 bits left for the fraction part, and you'll get 1023.700012207, just a little too high.
A “double” has 53 bits of precision and 11 bits for the exponent. Using double precision decreases the magnitude of the issue, but it never goes away.
The Alteryx Formula Tool does all its operations on numbers as double precision. However, if numbers come in as single precision “float” numbers the Formula Tool cannot magically restore precision to the number. Once precision is lost, it is gone for good. For example, if you give it 4095.7 as a float and subtract 4095.7 as a double, you get -4.88e-05. With 1023.7 as float minus 1023.7 as double you get 1.22e-05.
This issue shows up again when you convert Float fields to strings or to Fixed Decimal formats. Of course, if your number is 4095.699951172, converting it to a string or a fixed decimal with one digit of precision will manage to produce “4095.7”. But what if your number is more like .65 ? Will it round to 0.6 or 0.7? Alas, it depends on how many bits you have left for the fraction. The closest Float to 4095.65 is about 4095.649902, a little low. When rounded to one digit after the decimal point you will get “4095.6”. The closest Float to 1023.65 is about 1023.650024, a little high. When rounded to one digit after the decimal point you will get “1023.7”. That’s a computer for you.
Interested in learning more on data types in general? Check out Alteryx Data Types 101.
You've saved my sanity. Well written and helpful thanks a bunch.
Makes sense but is really weird to think about ! thanks @ChrisK