Calculation errors with dynamic formulas

[MaximilianKodat]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.1.3

Spreadsheet application

Excel

Description

Hello,
we (AMANA) are facing some problems regarding dynamic formulas.
Some results of formulas which are containing ranges are not resolved correctly. We developed a fix for dynamic formulas some time ago prior to the rework of the formula engine. See #769
We have now tried our unit test against the current version of EPPlus without success.
Please find our unit test and the used testfile below:

        [TestMethod]
        public void Issue_768_Dynamic_Array_Formulae()
        {

            //Issue: If two namedRanges (columns with Names) are calculated like "=range1 + range2" Only the first row of the ranges are calculated and the result is copied to the rest of the rows from the resultcolumn. 

#if Core
            var dir = AppContext.BaseDirectory;
            dir = Directory.GetParent(dir).Parent.Parent.Parent.FullName;
#else
            var dir = AppDomain.CurrentDomain.BaseDirectory;
#endif
            var p = new ExcelPackage(new FileInfo(Path.Combine(dir, "Workbooks", "DynamicArrayFormulae.xlsx")));

            var ws = p.Workbook.Worksheets.First();
            ws.Calculate();

            //range in range in Fomular
            Assert.AreEqual(311d, ws.Cells["C1"].Value);
            Assert.AreEqual(306d, ws.Cells["C2"].Value);

            //range1+range2 horizontal
            Assert.AreEqual(103d, ws.Cells["C3"].Value);
            Assert.AreEqual(104d, ws.Cells["C4"].Value);
            Assert.AreEqual(105d, ws.Cells["C5"].Value);
            Assert.AreEqual(106d, ws.Cells["C6"].Value);
            Assert.AreEqual(107d, ws.Cells["C7"].Value);
            Assert.AreEqual(108d, ws.Cells["C8"].Value);
            Assert.AreEqual(109d, ws.Cells["C9"].Value);
            Assert.AreEqual(110d, ws.Cells["C10"].Value);

            Assert.AreEqual(112d, ws.Cells["C12"].Value);
            Assert.AreEqual(113d, ws.Cells["C13"].Value);
            Assert.AreEqual(114d, ws.Cells["C14"].Value);

            //range3+range4 vertical
            Assert.AreEqual(101d, ws.Cells["F21"].Value);
            Assert.AreEqual(102d, ws.Cells["G21"].Value);
            Assert.AreEqual(103d, ws.Cells["H21"].Value);
            Assert.AreEqual(104d, ws.Cells["I21"].Value);
            Assert.AreEqual(105d, ws.Cells["J21"].Value);
            Assert.AreEqual(106d, ws.Cells["K21"].Value);
            Assert.AreEqual(107d, ws.Cells["L21"].Value);
            Assert.AreEqual(108d, ws.Cells["M21"].Value);
            Assert.AreEqual(109d, ws.Cells["N21"].Value);
            Assert.AreEqual(110d, ws.Cells["O21"].Value);
            Assert.AreEqual(111d, ws.Cells["P21"].Value);
            Assert.AreEqual(112d, ws.Cells["Q21"].Value);
            Assert.AreEqual(113d, ws.Cells["R21"].Value);

            //When Issue_WithRangeCalculation_IF
            Assert.AreEqual(306d, ws.Cells["H2"].Value);
            Assert.AreEqual(103d, ws.Cells["H3"].Value);
            Assert.AreEqual(104d, ws.Cells["H4"].Value);
            Assert.AreEqual(105d, ws.Cells["H5"].Value);

            Assert.AreEqual(100d, ws.Cells["I2"].Value);
            Assert.AreEqual(100d, ws.Cells["I3"].Value);
            Assert.AreEqual(100d, ws.Cells["I4"].Value);
            Assert.AreEqual(100d, ws.Cells["I5"].Value);

            Assert.AreEqual(100d, ws.Cells["J2"].Value);
            Assert.AreEqual(100d, ws.Cells["J3"].Value);
            Assert.AreEqual(100d, ws.Cells["J4"].Value);
            Assert.AreEqual(100d, ws.Cells["J5"].Value);

            Assert.AreEqual("Falsche Auswahl", ws.Cells["K2"].Value);
            Assert.AreEqual("Falsche Auswahl", ws.Cells["K3"].Value);
            Assert.AreEqual("Falsche Auswahl", ws.Cells["K4"].Value);
            Assert.AreEqual("Falsche Auswahl", ws.Cells["K5"].Value);


            //Normal
            Assert.AreEqual(198d, ws.Cells["C18"].Value);

            //String
            Assert.AreEqual("#VALUE!", ws.Cells["C19"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["C15"].Value.ToString());

            //Empty Cell
            Assert.AreEqual(100d, ws.Cells["C11"].Value);
            Assert.AreEqual(20d, ws.Cells["C20"].Value);

            //OutOfRange IF
            Assert.AreEqual("#VALUE!", ws.Cells["H1"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["I1"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["J1"].Value.ToString());
            Assert.AreEqual("Falsche Auswahl", ws.Cells["K1"].Value);
            Assert.AreEqual("#VALUE!", ws.Cells["H6"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["I6"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["J6"].Value.ToString());
            Assert.AreEqual("Falsche Auswahl", ws.Cells["K6"].Value);

            //OutOfRange Normal
            Assert.AreEqual("#VALUE!", ws.Cells["C16"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["E21"].Value.ToString());
            Assert.AreEqual("#VALUE!", ws.Cells["S21"].Value.ToString());

            //UseAGAIN
            Assert.AreEqual(206d, ws.Cells["F2"].Value);
            Assert.AreEqual(3d, ws.Cells["F3"].Value);
            Assert.AreEqual(4d, ws.Cells["F4"].Value);
            Assert.AreEqual(5d, ws.Cells["F5"].Value);
            //UseIFAGAIN
            Assert.AreEqual(306d, ws.Cells["M2"].Value);
            Assert.AreEqual(103d, ws.Cells["M3"].Value);
            Assert.AreEqual(104d, ws.Cells["M4"].Value);
            Assert.AreEqual(105d, ws.Cells["M5"].Value);
            Assert.AreEqual("#VALUE!", ws.Cells["M6"].Value.ToString());


            //Check if something in if is fixed wrong
            Assert.AreEqual(2d, ws.Cells["F11"].Value);
            Assert.AreEqual(1d, ws.Cells["F12"].Value);
        }

DynamicArrayFormulae.xlsx

[swmal]

Thanks for reporting this, we will have a look and will get back to you here.

[swmal]

@MaximilianKodat just an update - we have tested this and can see that EPPlus doesn't calculate these named ranges with implicit intersection as expected. We will dive further into it and will try to fix it.

[MaximilianKodat]

@swmal Thanks for the update. Looking forward to hear from you again.

[JanKallman]

This is now fixed in EPPlus 7.2.1