Excel VBA – Floating point numbers in Excel – not exact

Everyone works with floating point numbers daily.

If you go at a supermarket, the prices are like this: 11 eur 29 cents, 23 eur 35 cents and etc. As a C# developer and a blogger, I knew that there is some kind of a problem with the floating points and that the accuracy, but I did not realize how big it was. I thought that it only happens on some floating point numbers or similar.

MS_Excel

 

The truth is unfortunately not a pleasant one – it happens a lot in plenty of calculation with floating points. E.g., if you sum 0,10 with any number above 3, you would definitely not get number + 0,10.

E.g. 10,1-10 is not 0,1!

And that’s enough to say that the calculations are wrong.

Actually, this is not only a bug in Excel, that’s a well-known bug in every computer language, due to the logic of the floating point numbers. In integer representation, every number is represented like the sum of the powers of 2.

E.g., the number 43 is represented as 1+2+8+32. Or (2^0+2^1+2^3+2^5). Not more and not less than exactly 43. What happens, when we want to present decimal numbers? The idea, behind the floating point numbers, is that a floating point numbers are represented like this: 1/2 + 1/4 + 1/8 etc. Thus, the number 0.875 is easily represented as 1/2+1/4+1/8. Or 1/2^1+1/2^2+1/2^3. Quick and simple. And nice.

However, it is really nice to have a number like 0.875, which can be represented easily as sum of 1 divided by some power of 2. But what happens, when we are not that lucky? And we have a number like 0,1? Which is rather often, when we work with decimal? The answer is only one – the computers do not calculate exactly but they give some kind of approximation. And this approximation leads to some interesting facts, that are against mathematics. For example in Excel 12.1-12 is different from 0.1. The reason for this is that 12.1-12 is not 0.1, but 0.99999999999996, because of the problem with the presentation of the floating point numbers.  And this is different from 0.1. See the formula yourself:

numbers

You may ask yourself, how big is this problem? The answer is – at least as big as infinity. I will confess, I really thought that it does not happen so often, until it happened to me 🙂 Thus, I have made a small research and even built some code to help me calculate ranges of the problem. Here is what the code calculates:

numbers2

With other words, for every number, between 4 and plus infinity, with a step of 0.1 Excel (and all the computer languages using doubles) makes an error in a simple calculation.

Simple as this one – 5.6-5.5. It is equal to 0.0999999999960 and not 0.1. In the next calculation, 5.7-5.6 we get 0.100000000000001 which is also not 0.1. I have decided to make the code, just to see how many “mistakes” are out there and it turned out that they are really in every number. Check it yourself:

That’s true, decimals are not exact. And should not be compared.

Anyway, there are always ways to get around this. Excel has introduced the following:

It works quite ok, rounding the numbers to what you see. In C#, we have the decimal variable, which is created exactly to cope up with this problem. And in any other language, we have  a work around this. But still, before you see it, you never realize how big this problem is. 🙂 Here is what Microsoft says about the floating-point arithmetics.

About

VBA Developer

Tagged with: , ,