Formula cell in excel shows #NAME?

Question

I am attempting to add an excel formula to calculate Upgrade Effort based on certain conditions that will refer to other columns in the same excel sheet.

Below is my formula

=@IFERROR(@IFS(C3="css", IFS(D3>0.5, "High",
 D3>0.3, "Medium", D3>0.2, "Low",D3>0,"Very Low", 
 D3=0, "None")),
 IFS(D3>0.5,"High",D3>0.3,"Medium",D3>0.2,"Low",D3>0,
 "Very Low",D3=0,"None"))

The value of a formula cell is not evaluated. When you select a cell and press enter, a popup warning appears, and the formula is correctly evaluated.

Sample code

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcekFormula {

    public static void main(String[] args) throws Exception {

        try (Workbook workbook = new XSSFWorkbook();
                FileOutputStream fileout = new FileOutputStream("ItemFilter.xlsx")) {

            Sheet dataSheet = workbook.createSheet("Data");

            Row row;
            Cell cell;
            String strFormula = "IFERROR(IFS(%s=\"css\", IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\")),IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\"))\r\n"
                    + "";
            Object[][] data = new Object[][]
                { new Object[]
                            { "Header", "Complexity", "Type of File", "Change", "Upgrade Effort" },
                  new Object[]
                  { "A", "High", "css", "1" },
                  new Object[]
                  { "A", "Low", "txt", "0.4" },
                  new Object[]
                  { "C", "Moderate", "css", "0.4" },
                  new Object[]
                  { "D", "High", "txt", "0.7" },
                  new Object[]
                  { "A", "High", "css", "0.8" },
                  new Object[]
                  { "B", "Low", "css", "0.2" },
                  new Object[]
                  { "G", "Low", "css", "0.1" },
                  new Object[]
                  { "G", "High", "properties", "0.4" },
                  new Object[]
                  { "G", "High", "css", "1" },
                  new Object[]
                  { "G", "High", "css", "0.2" },
                  new Object[]
                  { "G", "Low", "jsp", "0.8" },
                  new Object[]
                  { "H", "Low", "jsp", "0.6" },
                  new Object[]
                  { "H", "Low", "html", "0.4" } };
            for (int r = 0; r < data.length; r++) {
                row = dataSheet.createRow(r);
                Object[] rowData = data[r];
                for (int c = 0; c < rowData.length; c++) {
                    cell = row.createCell(c);
                    if (rowData[c] instanceof String) {
                        cell.setCellValue((String) rowData[c]);
                    } else if (rowData[c] instanceof Number) {
                        cell.setCellValue(((Number) rowData[c]).doubleValue());
                    }
                }
                if (rowData.length < 5) {
                    cell = row.createCell(4);
                    String fileTypeCol = "C" + (r + 1);
                    String changeCol = "D" + (r + 1);
                    String formula = String.format(strFormula, fileTypeCol, changeCol, changeCol,
                            changeCol, changeCol, changeCol, changeCol, changeCol, changeCol,
                            changeCol, changeCol);
                    System.out.println("Formula ====== " + formula);
                    cell.setCellFormula(formula);
                }
            }

            XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

            workbook.write(fileout);
            fileout.close();
            Desktop.getDesktop().open(new File("ItemFilter.xlsx"));

        }

    }
}

Received outcome:

Answer 1

The Excel function IFS was introduced after publishing the Office Open XML file system in 2007. Thus not all spreadsheet applications will support this function. To mark this, such functions are prefixed by _xlfn. in file storage. That is in /xl/worksheets/sheet*.xml using Office Open XML *.xlsx.

Because of that prefix spreadsheet applications can detect a function that is not supported in the version of Open Office XML that they are currently running. See: Issue: An _xlfn. prefix is displayed in front of a formula

Since Apache POI directly writes into file storage /xl/worksheets/sheet*.xml, that prefix must be set using Cell.setCellFormula. Wrong is setCellFormula("...IFS(..."). Correct is setCellFormula("..._xlfn.IFS(...").

Aditional your code should set numeric values for numbers and not strings. Wrong: ...new Object[] { "A", "High", "css", "1" }, new Object[] { "A", "Low", "txt", "0.4" },.... Correct ...new Object[] { "A", "High", "css", 1 }, new Object[] { "A", "Low", "txt", 0.4 },....

Complete example using your code sample:

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcelFormulaIFS {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("./IFSFormula.xlsx")) {

   Sheet dataSheet = workbook.createSheet("Data");

   Row row;
   Cell cell;
    //String strFormula = "IFERROR(IFS(%s=\"css\", IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\")),IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\"))\r\n"
    //        + "";
   String strFormula = "IFERROR(_xlfn.IFS(%s=\"css\", _xlfn.IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\")),_xlfn.IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\"))";
   Object[][] data = new Object[][] {
    new Object[] { "Header", "Complexity", "Type of File", "Change", "Upgrade Effort" },
    new Object[] { "A", "High", "css", 1 },
    new Object[] { "A", "Low", "txt", 0.4 },
    new Object[] { "C", "Moderate", "css", 0.4 },
    new Object[] { "D", "High", "txt", 0.7 },
    new Object[] { "A", "High", "css", 0.8 },
    new Object[] { "B", "Low", "css", 0.2 },
    new Object[] { "G", "Low", "css", 0.1 },
    new Object[] { "G", "High", "properties", 0.4 },
    new Object[] { "G", "High", "css", 1 },
    new Object[] { "G", "High", "css", 0.2 },
    new Object[] { "G", "Low", "jsp", 0.8 },
    new Object[] { "H", "Low", "jsp", 0.6 },
    new Object[] { "H", "Low", "html", 0.4 }
   };
   for (int r = 0; r < data.length; r++) {
    row = dataSheet.createRow(r);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof String) {
      cell.setCellValue((String) rowData[c]);
     } else if (rowData[c] instanceof Number) {
      cell.setCellValue(((Number) rowData[c]).doubleValue());
     }
    }
    if (rowData.length < 5) {
     cell = row.createCell(4);
     String fileTypeCol = "C" + (r + 1);
     String changeCol = "D" + (r + 1);
     String formula = String.format(strFormula, fileTypeCol, changeCol, changeCol,
                    changeCol, changeCol, changeCol, changeCol, changeCol, changeCol,
                    changeCol, changeCol);
     System.out.println("Formula ====== " + formula);
     cell.setCellFormula(formula);
    }
   }

   workbook.write(fileout);
   fileout.close();
   Desktop.getDesktop().open(new File("./IFSFormula.xlsx"));

  }
 }
}