Only value 87.15000000000001 results in error

Joriske

On a Posted Purchase Invoice, the value for "Direct Unit Cost Exc." has a value of 87,15.
When exporting this document to excel, it shows as "87.15000000000001". Formating the cell to number or currency or value, it shows up as 8.715.000.000.000.000.

In SQL (database collation is Polish_CI_AS), the value is shown as "87.15000000000000000000". This seems correct to me.

When I change the value (using UPDATE - SET instruction in SQL) to any other valy than 87.15x, using standard functionallity in Navision to export to excel, the number is shown correct in excel. From the moment I change the value again to 87.15xx, the error re-appears.

I've tried to export to Excel 2003 as wel as to Excel 2010; both versions resulted in same behaviour.
Same behaviour is also on other fields.

I can reproduce the error over and over again.

I can't figure out why this weird behaviour only appears with "87,15xx"

Can this be caused by the database collation (Polish_CI_AS)? Is there any other explanation?
And most of all, is there a solution to this problem?

Kind regards

Sog

The error is the 87.15000000000001 value. A rounding issue this low is usually a hickup form the cpu calculating an incorrect float.
The value in the db should be 87.15 and maximum 87.15000.
For the export to excel that is a local issue, 87.15 in EU is 87,15. Excel keeps that in mind an will see "." as a thousand seperator and "," as a decimal seperator. (which is vice versa in US)

Joriske

Hi Sog.
Thanks for your reply, but I'm not sure if this is the answer/solution to my problem.

In Navision, the value of "87.15" is entered (the fields have no settings / only default setting for decimals and rounding)
Everywhere in Nav, the number is (correctly) shown as 87.15. All calculations in Nav using this value are corect.
In SQL the value is shown as "87.15000000000000000000", which is still correct (for instance, if the value would have been 12,21 it would be shown in SQL as 12.21000000000000000000)

When I use a copy/past from Navision (posted purchase invoice lines), all data is copied correctly.

But when I use the "export to Excel" function, the weird number comes up as "87.15000000000001". (now with an ending/rounding? 1 at the end)
I reproduced this behaviour over and over again, with different Locale - settings (US, Dutch, Polish-as the data is in a Polish DB)

The weird thing is, it only happens with "87,15xx", not with "87,14xx" for instance.
Weird and anoying

Sog

I checked and you are correct, it's indeed 87.15000000000 in the database.
So forget everything else I wrote except the float error.
This fault seems very low level and the source might be either the cpu or the export to excel functionality or excel itself.
If you can reproduce the error on another cpu in a standard cronus db, you've found yourself a bug that may be sent to MS.
However since this is very low level, a solution might never appear.