Numeric operation on array inside function behaves incorrectly

[craigbrown]

If you have a function like SUM which takes an array, but on the array that you pass in you do a numeric operation with a constant value, e.g.

=SUM(A1:A7*1)

then Excel will perform that operation on each item in the array and add them together. So the example above would be calculated like:

=SUM(A1*1,A2*1,A3*1, ...)

However EPPlus takes just the first item of the array. So the example is instead calculated as:

=SUM(A1*1)

I think EPPlus should be doing the same thing as Excel. I've included some unit tests of a few different scenarios.

Unit Tests

[TestMethod]
public void Sum_CellReferenceRange_NumericOperationOnConstant_1()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = 2;
        sheet1.Cells["A2"].Value = 3;
        sheet1.Cells["A3"].Value = 6;
        sheet1.Cells["C10"].Formula = "SUM(A1:A7*1)";

        pck.Workbook.Calculate();

        Assert.AreEqual(11, sheet1.Cells["C10"].GetValue<double>(), 1E-10);
    }
}

[TestMethod]
public void Sum_CellReferenceRange_NumericOperationOnConstant_2()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = 2;
        sheet1.Cells["A2"].Value = 3;
        sheet1.Cells["A3"].Value = 6;
        sheet1.Cells["C10"].Formula = "SUM(A1:A7+1)";

        pck.Workbook.Calculate();

        Assert.AreEqual(18, sheet1.Cells["C10"].GetValue<double>(), 1E-10);
    }
}

[TestMethod]
public void Sum_NamedRange_NumericOperationOnConstant_1()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = true;
        sheet1.Cells["A2"].Value = true;
        sheet1.Cells["A3"].Value = false;
        sheet1.Cells["A4"].Value = true;
        sheet1.Cells["A5"].Value = false;
        sheet1.Cells["A6"].Value = true;
        sheet1.Cells["A7"].Value = true;
        sheet1.Names.Add("MyRange1", sheet1.Cells["A1:A7"]);
        sheet1.Cells["C10"].Formula = "SUM(MyRange1*1)";

        pck.Workbook.Calculate();

        Assert.AreEqual(5, sheet1.Cells["C10"].GetValue<double>(), 1E-10);
    }
}

[TestMethod]
public void Sum_NamedRange_NumericOperationOnConstant_2()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = true;
        sheet1.Cells["A2"].Value = true;
        sheet1.Cells["A3"].Value = false;
        sheet1.Cells["A4"].Value = true;
        sheet1.Cells["A5"].Value = false;
        sheet1.Cells["A6"].Value = true;
        sheet1.Cells["A7"].Value = true;
        sheet1.Names.Add("MyRange1", sheet1.Cells["A1:A7"]);
        sheet1.Cells["C10"].Formula = "SUM(MyRange1+1)";

        pck.Workbook.Calculate();

        Assert.AreEqual(12, sheet1.Cells["C10"].GetValue<double>(), 1E-10);
    }
}

[swmal]

This is something that we have started to work on, it is a part of supporting arrayformulas in EPPlus. When this is implemented you will also be able to apply operators on multiple ranges, i.e. "A1:A3 * B1:B3" and it will support functions that returns ranges instead of single values such as TRANSPOSE. I cannot give you an ETA at this stage, but we will start focus on it when we have launched the next major version in March.

[JanKallman]

This is implemented in EPPlus 7