MaxIfs/MinIfs returns #N/A for ">0" criteria

[colbybhearn]

I believe I'm seeing an issue with Maxifs and Minifs in EPPlus where they mistakenly return #N/A
In terms of the MS documentation (https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)

• min_range is a range numbers,
• criteria_range1 a range of numbers (same range as min_range), and
• criteria is ">0"

Excel came up with the expected value. I tried rolling back to EPP 5.8.5 and 5.8.4, but neither behaved any better. I'm not seeing in GetMatches() where the > operator is supported, but I could be missing that. Hopefully this is a simple fix!

Here are two complete examples by which you can reproduce this:

A) Additional unit test for MinAndMaxIfsTests.cs
Method:

        [TestMethod]
        public void MinIfsShouldWork()
        {
            _worksheet.Cells["F1"].Formula = "MAXIFS(D3:D7, D3:D7,\">0\")";
            _worksheet.Calculate();
            Assert.AreEqual(93d, _worksheet.Cells["F1"].Value);
        }

Output: (yes, 93 should be 93d, but that's irrelevant)

B) standalone test code
Code:

        ExcelPackage.LicenseContext = LicenseContext.Commercial;
        string path = @".\Minifs.xlsx";
        using (var package = new ExcelPackage(new FileInfo(path)))
        {
            ExcelWorksheet est = package.Workbook.Worksheets["Sheet1"];
            trace(est.Cells["F4"]);
            package.Workbook.Calculate();
            trace(est.Cells["F4"]);
            Console.WriteLine("");
        }

        public static void trace(ExcelRange c)
        {
            if(c.Value == null)
                System.Diagnostics.Trace.WriteLine($"{c.Address.ToString()} is null");
            else
                System.Diagnostics.Trace.WriteLine(c.Address.ToString() +"\t" +c.Formula + "\t" + c.Value.ToString());
        }

Input File: Minifs.xlsx

Output:

F4	_xlfn.MAXIFS(Values,Values,">0")	89
F4	_xlfn.MAXIFS(Values,Values,">0")	#N/A

Thank you.
-Colby

[swmal]

Thanks Colby, we'll have a look at this.

[swmal]

This should be fixed now, and can be tested via our developerment nuget feed: https://github.com/EPPlusSoftware/EPPlus/wiki/Using-our-Develop-Nuget-Feed
Will be included in the next release.

Thank you for a very detailed description and code to replicate the issue.

/Mats