Excel and floating point problem?

[Xiq]

Hello everyone, its been a while since my last post on this forum

I found a frustrating problem (first time I noticed this after all these years using Excel), for example:

=5.1-5

I would expect the result to be exactly 0.1
however Excel is returning 0.0999999999999996

So my test:

=5.1-5=0.1

Will of course return FALSE...

I assume this has to do something with floating point stuff (see here a MS support page).

Is there a better solution to this problem, other than using the ROUND function and hardcode the number of decimals to round? Using the "Precision as displayed" option sounds a bit tricky to use in my opinion.

 

[NARAYANK991]

Hi ,

You can test out certain values of the low value , 0.1 in your example.

The problem exists till a decimal value of less than 0.5.

Once the low value is 0.5 or above , the problem does not exist.

Thus , even a subtraction of 99999999999999.5 - 99999999999999 returns the correct value of 0.5

The moment the low value becomes less than 0.5 , say 0.49 , then the result of :

9999999999999.49 - 9999999999999

does not equal 0.49

One solution is to multiply by 10 or even 10000 , and then divide the result by 10 or 10000 ; this will give you the correct result.

Thus , =(9999999999999.49 * 100 - 9999999999999 * 100) / 100 will correctly return 0.49

Narayan

 

[Xiq]

Hi NARAYANK991,

Unfortunately this is not a solution that works 100% of the time.

For example:

=64.6*1000-64*1000=0.6*1000

Will return:

=599.999999999993=600

I've attached a test with 1k comparisons.

 

[NARAYANK991]

Hi ,

Will this technique work ?

Narayan

 

[GraH - Guido]

Hi Xiq, in this post https://chandoo.org/forum/threads/largest-number.37272/#post-224019, FP precision was also discussed. You might be interested in the add-ins I referred too.