Rounding errors are boosted by ROUNDDOWN, VLOOKUP, etc.

[helmkebecksche]

NPOI Version

2.7.1

File Type

• XLSX
• XLS
• DOCX
• XLSM
• OTHER

Upload the Excel File

example.xlsx

Reproduce Steps

Load the file or create it via code. Set the readFromFile as desired:

            const bool readFromFile = true;

            NPOI.XSSF.UserModel.XSSFWorkbook workbook;
            NPOI.SS.UserModel.ICell cell1, cell2, cell3;

            if (readFromFile)
            {
                const string filepath = @"example.xlsx";
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(filepath);
                var sheet = workbook.GetSheet("Test");

                cell1 = sheet.GetRow(0).GetCell(0);
                cell2 = sheet.GetRow(1).GetCell(0);
                cell3 = sheet.GetRow(2).GetCell(0);
            }
            else
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                var sheet = workbook.CreateSheet("Test");

                cell1 = sheet.CreateRow(0).CreateCell(0);
                cell2 = sheet.CreateRow(1).CreateCell(0);
                cell3 = sheet.CreateRow(2).CreateCell(0);

                cell1.SetCellValue(4.8);
                cell2.SetCellFormula("Test!A1-4");
                cell3.SetCellFormula("ROUNDDOWN(Test!A2, 2)");
            }

            var evaluator = new NPOI.XSSF.UserModel.XSSFFormulaEvaluator(workbook);
            evaluator.EvaluateAll();

            Console.WriteLine($"readFromFile: {readFromFile}");
            Console.WriteLine($"{cell2.Address}: expected 0.8 but was {cell2.NumericCellValue}");
            Console.WriteLine($"{cell3.Address}: expected 0.8 but was {cell3.NumericCellValue}");

There will be a console output:

A2: expected 0.8 but was 0,7999999999999998
A3: expected 0.8 but was 0,79

Issue Description

In the example file, the difference between A1 (value: 4.8) and the integer 4 is calculated.

The expected result is 0.8 but the actual result is 0.7999999999999998. (see cell A2)
At first glance, the difference is insignificant.

If you continue computing with this value, some functions like ROUNDDOWN (see cell A3) or VLOOKUP boost this difference in a way that it becomes significant (see console output).

How can this problem be tackled? Is there a possibility to eliminate the small differences, maybe by automatically rounding before the evaluation step?

[tonyqus]

#1198.

[helmkebecksche]

Hi tonyqus,

thanks for looking after my issue.

Unfortunately, the problem not only occurs in 2.7.1 but also in the current version 2.7.2. The output is still the following using 2.7.2:

A2: expected 0.8 but was 0,7999999999999998
A3: expected 0.8 but was 0,79

The issue does not seem to be related to ROUNDDOWN but to rounding errors that arise from calculations. The ROUNDDOWN function is only an example of how the rounding errors can (unintentionally) be boosted in practice.

I tried some more calculcations. Some of them also result in rounding errors whereas some others give out the exact result:

0.0 + 0.8 : exactly 0.8
0.2 + 0.6 : exactly 0.8
4.9 – 4.1 : 0.80000000000000071
4.7 – 3.9 : 0.80000000000000027
1.5 – 0.7 : exactly 0.8
0.2 * 4.0 : exactly 0.8
3.2 * 0.25 : exactly 0.8
5.6 / 7.0 : 0.79999999999999993
8.0 / 10.0 : exactly 0.8

[Bykiev]

I did some reserch and the issue is not in ROUNDDOWN function, but in substraction, the result of calculation 4.8 - 4.0 in .net is 0.79999999999999982.
@tonyqus, seems we should check if the value contains fractional part and if so, convert to double, otherwise to integer, what do you think? Or should we switch to decimal, instead of double?

[tonyqus]

@Bykiev I'm a bit busy this month since I'm just getting on board in Malaysia. I will take some time to investigate this issue in January.