Accuracy bug? 
Post Reply 
Author  
T. Parass
New Member Joined: 28 Jun 2019 Posts: 2 
Post Options
Quote Reply
Topic: Accuracy bug? Posted: 02 Jul 2019 at 3:16pm 

Hello all,
I seemed to uncover a simple calculation inaccuracy... at the very least it's visibly different than Excel. Here's a sample C# console application that demonstrates the issue: class BugDemo { public static void Main(string[] args) { XlsFile xls = new XlsFile(1, TExcelFileFormat.v2016, true); // same for all formats: v2019, v2016, v2013, v2010, v2007, and v2003 xls.SetCellValue(1, 1, new TFormula("=1  0.9999")); // result should be 0.0001 // if I don't save, I get a 0 back ?! xls.Save(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "bug2.xlsx")); Console.WriteLine(xls.GetCellValue(1, 1)); // Excel shows 1E04 or 0.0001 if format set as such // FlexCel shows 9.9999999999989E05 (0.0000999999999999...) < this is the bug in question } } I understand the inaccuracies inherent with representing base 10 decimals in base 2 floating point (.NET Double, in this case), but in our case the difference with Excel has become significant. Is there anything that can be done in this case to mirror Excel's calculation result? Thanks! ..anthony 

Adrian Gallero
TMS Support Joined: 18 May 2010 Posts: 1282 
Post Options Quote Reply Posted: 03 Jul 2019 at 1:50am  
Hi,
The problem is that we are not the ones doing the calculations, .NET is. And you can see easily where the problem comes from. Create a new console app, and write the following code:
I see this result in the console: 9.9999999999989E05 Press any key to continue . . . So unless we did our own floating point operations (bypassing .NET and the FPU) there is not really much we can do about it. But note that Excel also uses 8bytedoublepresicion numbers in all of its calculations, same as C# and us. So you can bet they have the same issues as us. Excel also has internally 9.9999999999989E05, you are just not having enough precision to display it. Format the cell A1 in Excel as a number with 18 decimal places (that's what Console.WriteLine shows), and you'll see it: If you prefer, you could do:
And it will print 0.0001 Press any key to continue . . . But once again, both Excel and FlexCel (via C#) internally store 9.9999999999989E05 in the cell. If you do a console.WriteLine with a double, it will print just that. But if you format the number to a "general" format, both FlexCel and Excel will show 0.0001, because 0.00009999999 rounds to 0.0001 if you have not enough digits. A final note: FlexCel does a lot of work under the hood to try to replicate as much as possible the Excel precision (and loss of) but Excel seems to handle thousands of particular cases especially, probably to avoid people complaining about it, and we just use the default C# calculation results. Because it is just not possible to manually fix every case, and when you fix one you break another. Binary floating point numbers just don't get the results you expect in a decimal system. They are as correct as possible, but it is just hard for us to make our heads around them, so I really wish Excel (And C#, and calculators) used a base10 floating point. But it is just the way it is. I mean, open Excel and write "=0.1+0.20.3=0" in a cell, and you will see it is false. With a decimal floating point, 1/3+1/3+1/3 wouldn't be 1, but 0.1+0.20.3 for sure would be 0. 

T. Parass
New Member Joined: 28 Jun 2019 Posts: 2 
Post Options Quote Reply Posted: 24 Jul 2019 at 10:55pm  
Thanks so much Adrian for the comprehensive follow up! We are aware of the limitations of decimal values in a binary floating point representation, but we weren't aware that Excel too was using IEEE 754 doubleprecision floating point values. The observed differences implied a different internal representation, but I can appreciate the enormity of coding special cases (for Excel) and the effort in attempting to emulate them (for FlexCel).
Thanks again, ..anthony p.s. I was away and couldn't reply right away, but your fast response is still appreciated. Keep up the great work.


Post Reply 
Forum Jump  Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum 