-
Bug
-
Resolution: Fixed
-
Major
-
5.11.0
-
None
-
Security Level: Jimmy
-
None
-
None
Steps to Reproduce
1. compare B2 between Excel and keikai
Current Result
Excel==>0.666666666666667
keikai=>0.666666666666668
Expected Result
both values are the same
Debug Information
- according to https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result, we assume Excel only stores 15 digits of precision. So keikai also does the same. But it looks like Excel calculates it with the 17 digits number and turns it into 15 digits number for display. Keikai calculates it with 15 digits number
- if you rename the attached file to zip and extract it, check sheet1.xml, you will find the file content at A1 is 0.16666666666666666 (17 digits of precision), so Excel's result is calculated based on 17 digits and rounds the result to 15 digits.
- https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
- related discussion https://answers.microsoft.com/en-us/msoffice/forum/all/why-does-excel-numeric-values-lose-precision-but/aba0fce4-5bf7-4e9e-a56d-b7e2b9b79c8b
- Excel truncates manually-entered numbers (including those read from CSV and TXT files) to the first 15 significant digits
Alternative
According to correcting-precision-errors , using ROUND() to correct this kind of error.