Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Accuracy bug?

 Post Reply Post Reply
Author
T. Parass View Drop Down
New Member
New Member
Avatar

Joined: 28 Jun 2019
Posts: 2
Post Options Post Options   Quote T. Parass Quote  Post ReplyReply Direct Link To This Post 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 1E-04 or 0.0001 if format set as such
                                                   // FlexCel shows 9.9999999999989E-05 (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

Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1226
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post 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:

using System;

namespace ConsoleApp39
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(1 - 0.9999);
        }
    }
}


I see this result in the console:
9.9999999999989E-05
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  8-byte-double-presicion 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.9999999999989E-05, 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:

using FlexCel.Core;
using FlexCel.XlsAdapter;
using System;

namespace ConsoleApp39
{
    class Program
    {
        static void Main(string[] args)
        {
            XlsFile xls = new XlsFile(1, TExcelFileFormat.v2016, true); // same for all formats: v2019, v2016, v2013, v2010, v2007, and v200
            //Note: It is indeed the same for all formats, formats only affect default fonts and colors, not recalculation.

            xls.SetCellValue(1, 1, new TFormula("=1 - 0.9999")); // result should be 0.0001

            // if I don't save, I get a 0 back ?!
            //There is no need to save, but you need to recalculate. FlexCel automatically recalculates on save,
            //But it doesn't recalculate every time you change a cell, as it would be slow.
            //You can do: 
            // xls.RecalcMode = TRecalcMode.OnEveryChange
            //if you want FlexCel to recalculate every time you change a cell, but this will slow down the 
            //app a lot.

            xls.Recalc();

            Console.WriteLine(xls.GetStringFromCell(1, 1));
        }
    }
}

And it will print 
0.0001
Press any key to continue . . .

But once again, both Excel and FlexCel (via C#) internally store 9.9999999999989E-05 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 base-10 floating point. But it is just the way it is. I mean, open Excel and write "=0.1+0.2-0.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.2-0.3 for sure would be 0.

Back to Top
T. Parass View Drop Down
New Member
New Member
Avatar

Joined: 28 Jun 2019
Posts: 2
Post Options Post Options   Quote T. Parass Quote  Post ReplyReply Direct Link To This Post 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 double-precision 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down