After executing the code public static void main(String args[]) throws IOException { //with #VALUE! error (1) createExample(new FileOutputStream("1.xls"), new HSSFWorkbook()); //no error (2) createExample(new FileOutputStream("1.xlsx"), new XSSFWorkbook()); } private static void createExample(FileOutputStream file, Workbook wb) throws IOException { Sheet sh = wb.createSheet("sheet"); Row row = sh.createRow(0); Cell cell1 = row.createCell(0); cell1.setCellValue(100); Cell cell2 = row.createCell(1); cell2.setCellFormula("RANK(A1,(A1:A1),0)"); wb.write(file); } we've got the following result for line (1) the #VALUE! error in the generated excel file. This error could be corrected manually if we position the cursor into the formula bar and press "Enter". For line (2) there is no error.
See http://poi.apache.org/spreadsheet/eval.html - you need to evaluate the formulas after setting them, to ensure the cached value is correctly set and available to excel.