IFS formula has #NAME? error

[coxymla]

I was trying to insert a IFS formula and when I open the workbook in Excel the cells have a #NAME? error. The actual formula appears to be valid because when I click in the cells and out again, the formula is accepted.

I saved the workbook after "fixing" the cells in this way and then reflected the workbook in Productivity Tool. The fixed workbook uses _xlfn.IFS in the XML.

Test that reproduces this issue:

private static void FormulaBug()
{
    string GetPercentageFormula(int firstCol, int secondCol, int excelRow)
    {
        return String.Format(@"IFS(R{0}C{1}="""","""",R{0}C{2}="""","""",1,IFERROR((R{0}C{1} - R{0}C{2}) / R{0}C{1}, 100%))", excelRow, firstCol, secondCol);
    }

    string GetDifferenceFormula(int firstCol, int secondCol, int excelRow)
    {
        return String.Format(@"IFS(R{0}C{1}="""","""",R{0}C{2}="""","""",1,R{0}C{1} - R{0}C{2})", excelRow, firstCol, secondCol);
    }

    var outputFilePath = @"C:\dev\PivotBug\FormulaBugWorkaround.xlsx";
    File.Delete(outputFilePath);
    var workbookFile = new FileInfo(outputFilePath);
    using (var excel = new ExcelPackage(workbookFile))
    {
        var wb = excel.Workbook;
        var ws = wb.Worksheets.Add("foo");

        int excelRow = 1;
        int excelCol = 1;

        {
            excelRow++;
            excelCol = 1;

            ws.Cells[excelRow, excelCol].Value = 100;
            int currentCol = excelCol;
            excelCol++;
            ws.Cells[excelRow, excelCol].Value = 50;
            int previousCol = excelCol;
            excelCol++;

            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetDifferenceFormula(currentCol, previousCol, excelRow);
            excelCol++;
            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetPercentageFormula(currentCol, previousCol, excelRow);
            excelCol++;
        }

        {
            excelRow++;
            excelCol = 1;

            ws.Cells[excelRow, excelCol].Value = null;
            int currentCol = excelCol;
            excelCol++;
            ws.Cells[excelRow, excelCol].Value = 50;
            int previousCol = excelCol;
            excelCol++;

            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetDifferenceFormula(currentCol, previousCol, excelRow);
            excelCol++;
            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetPercentageFormula(currentCol, previousCol, excelRow);
            excelCol++;
        }

        {
            excelRow++;
            excelCol = 1;

            ws.Cells[excelRow, excelCol].Value = 100;
            int currentCol = excelCol;
            excelCol++;
            ws.Cells[excelRow, excelCol].Value = null;
            int previousCol = excelCol;
            excelCol++;

            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetDifferenceFormula(currentCol, previousCol, excelRow);
            excelCol++;
            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetPercentageFormula(currentCol, previousCol, excelRow);
            excelCol++;
        }

        {
            excelRow++;
            excelCol = 1;

            ws.Cells[excelRow, excelCol].Value = 100;
            int currentCol = excelCol;
            excelCol++;
            ws.Cells[excelRow, excelCol].Value = 0;
            int previousCol = excelCol;
            excelCol++;

            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetDifferenceFormula(currentCol, previousCol, excelRow);
            excelCol++;
            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetPercentageFormula(currentCol, previousCol, excelRow);
            excelCol++;
        }

        {
            excelRow++;
            excelCol = 1;

            ws.Cells[excelRow, excelCol].Value = 0;
            int currentCol = excelCol;
            excelCol++;
            ws.Cells[excelRow, excelCol].Value = 50;
            int previousCol = excelCol;
            excelCol++;

            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetDifferenceFormula(currentCol, previousCol, excelRow);
            excelCol++;
            ws.Cells[excelRow, excelCol].FormulaR1C1 = GetPercentageFormula(currentCol, previousCol, excelRow);
            excelCol++;
        }

        excel.Save();
    }

    Process.Start(outputFilePath);
}

Workaround is to manually insert the _xlfn. string as part of the formula. i.e.:

string GetPercentageFormula(int firstCol, int secondCol, int excelRow)
{
    return String.Format(@"_xlfn.IFS(R{0}C{1}="""","""",R{0}C{2}="""","""",1,IFERROR((R{0}C{1} - R{0}C{2}) / R{0}C{1}, 100%))", excelRow, firstCol, secondCol);
}

string GetDifferenceFormula(int firstCol, int secondCol, int excelRow)
{
    return String.Format(@"_xlfn.IFS(R{0}C{1}="""","""",R{0}C{2}="""","""",1,R{0}C{1} - R{0}C{2})", excelRow, firstCol, secondCol);
}

[uskorc]

Maybe it helps someone else, the IFS is not supported in EPPlus. I got the same error when calling Calculate on the existing IFS formula in existing sheets. The workaround was to translate the IFS to a multiple IF formula.