Sum n largest numbers EPPlus

Question

I'm trying to sum the n largest numbers of a range.

My formula is: SUM(LARGE(A10:A15, {1,2}))

But I'm getting a #VALUE as result.

If I debug the formula execution to a log file I got that:

Worksheet: Sheet 1
Address: A9
OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE!
   em OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj)
   em OfficeOpenXml.FormulaParsing.Excel.Functions.ExcelFunction.ArgToInt(IEnumerable`1 arguments, Int32 index)
   em OfficeOpenXml.FormulaParsing.Excel.Functions.Math.Large.Execute(IEnumerable`1 arguments, ParsingContext context)
   em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionCompilers.DefaultCompiler.Compile(IEnumerable`1 children, ParsingContext context)
   em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()

Looks like the Large Function doesn't accept an array as a second argument.

How can I get the sum of n largest values formula in EPPlus?

Have tried in Excel? My Excel (2013) doesn't allow that syntax. You need to add a column with the n-largest numbers and sum over that. — Palle Due, Feb 19 '18 at 14:44
Yes, my Excel (2016) allow that syntax. Some reference: https://exceljet.net/formula/sum-top-n-values — Munir, Feb 19 '18 at 15:04

score 1 · Answer 1 · answered Feb 19 '18 at 18:26

I have Excel 2016 and EPPlus 4.1.1.0 and I tested your work and it is working perfectly.

My guess is either that your cells "A10:A15" hold incorrectly formatted values (Maybe strings), or you are using old version of EPPlus/Excel.

Please check your data by trying the formula on another new column holding numeric values, and try to update your EPPLUS version.

Code I used:

 using (ExcelPackage pkg = new ExcelPackage(new FileInfo(@"D:\testSheet.xlsx")))
        {
            pkg.Workbook.Worksheets.Add("sheet");
            pkg.Workbook.Worksheets.ElementAt(0).Cells["A10:A15"].Value = 2;
            pkg.Workbook.Worksheets.ElementAt(0).Cells["C5"].Formula = "SUM(LARGE(A10:A15, {1,2}))";
            pkg.Save();
        }

Result:

Could you test your example with Calculate() instead of Save() to a excel file? Cause I'm not writing a Excel File actually. If, in your example, I change pkg.Save(); for pkg.Workbook.Calculate(); the Cells["C5"].Value is "#VALUE". — Munir, Feb 22 '18 at 20:45

score 0 · Accepted Answer · answered Mar 01 '18 at 03:45

Looks like this kind of formula only works when save the excel to file. In my case I don't want a file, just the calculated value.

The solution was to extend de ExcelFunction class and create my own implementation:

public class SumLargest: ExcelFunction
{

    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        ValidateArguments(arguments, 2);

        //n largest values
        var n = ArgToInt(arguments, 1);

        var cells = ArgsToDoubleEnumerable(arguments, context);

        var values = cells.ToList();
        values.RemoveAt(values.Count() - 1);

        var result = values
            .OrderByDescending(x => x)              
            .Take(n)
            .Sum();

        return CreateResult(result, DataType.Decimal);
    }

}

Then I added it to my package:

using (ExcelPackage excelPackage = new ExcelPackage())
{
     excelPackage.Workbook.FormulaParserManager.AddOrReplaceFunction("SUMLARGEST", new SumLargest());
     ....
}

And I can use it in a much better way then the original formula:

Cells["C5"].Formula = "SUMLARGEST(A10:A15, 2)"

Reference: https://github.com/JanKallman/EPPlus/wiki/Implementing-missing-or-new-Excel-functions

score 0 · Answer 3 · answered Jul 14 '18 at 19:11

0

The logfile entry provides the information that the Formula parser fails to convert the content of cell A9 on worksheet 1 to an integer. This is an old thread, just thought that it could be useful to highlight if someone has a similar problem.

answered Jul 14 '18 at 19:11

swmal

269
1
6

Sum n largest numbers EPPlus

3 Answers3