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.
