This project is read-only.
1
Vote

Incorrect value of calculation with INT operator

description

In the following example I try to calculate different formulas, but the results of calculation are sometimes incorrects (and really understandables) :
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet();
            var row = sheet.CreateRow(0);
            var cell = row.CreateCell(0);
            var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

            // should be 6.0 => OK
            cell.SetCellFormula("INT(6.99)");
            var cellValue = evaluator.Evaluate(cell);
            var result = cellValue.NumberValue;

            // should be 6.0 => KO : 7.0 is returned
            var cell1 = row.CreateCell(1);
            cell1.SetCellFormula("INT(6.991)");
            var cellValue1 = evaluator.Evaluate(cell1);
            var result1 = cellValue1.NumberValue;

            // should be 6.0 => OK
            var cell2 = row.CreateCell(2);
            cell2.SetCellFormula("INT(6.9900000000000005)");
            var cellValue2 = evaluator.Evaluate(cell2);
            var result2 = cellValue2.NumberValue;

            // should be 6.0 => KO : 7.0 is returned
            var cell3 = row.CreateCell(3);
            cell3.SetCellFormula("INT(6.9900000000000007)");
            var cellValue3 = evaluator.Evaluate(cell3);
            var result3 = cellValue3.NumberValue; 
Am I doing something wrong ?

comments

brobic wrote Sep 8, 2016 at 10:06 AM

Hello,

I have exactly the same problem.

Have you found a solution to resolve it?

It generates multiple bugs in our application...

Thanks by advance.

ebyrob wrote Sep 15, 2016 at 4:02 PM

This is controlled in the source code here:

main\SS\Formula\Functions\Int.cs
public class Int : OneArg
{

    public override double Evaluate(double d)
    {
        if (d > 0)
            return Math.Round(d - 0.49);
        else
            return Math.Round(d - 0.5);
    }

}
Should be Math.Floor(d) instead of Round I think.

Scampeon wrote Sep 20, 2016 at 2:56 PM

Thanks a lot !

This code corrects the problem
    public class Int : OneArg
    {
        public override double Evaluate(double d)
        {
            return Math.Floor(d);
        }
    }
Both Excel and Math.Floor give the same result
- INT(-2)                  => -2
- INT(-1,9)                => -2
- INT(-2,1)                => -3
- INT(6,99)                => 6
- INT(6,991)               => 6
- INT(6,9900000000000005)  => 6
- INT(6,9900000000000007)  => 6

- Math.Floor(-2)                  => -2
- Math.Floor(-1.9)                => -2
- Math.Floor(-2.1)                => -3
- Math.Floor(6.99)                => 6
- Math.Floor(6.991)               => 6
- Math.Floor(6.9900000000000005)  => 6
- Math.Floor(6.9900000000000007)  => 6