Created attachment 37304 [details] The file used to reproduce the bug When I use HSSFCell#setCellFormula to modify the formula in a cell, if I just adjust some cells in the formula, e.g.for AVERAGE(A1:F1) to AVERAGE(A2:F1), HSSFCell#setCellFormula will be wrong modify the row of the second cell to the row of the first cell, but the first cell will not be modified! before fixing: AVERAGE(A1:F1) Expected value: AVERAGE(A2:F1) Actual value: AVERAGE(A1:F2) Below is the test code to reproduce this problem: public static void main(String[] args) throws IOException { Workbook workbook = WorkbookFactory.create(new File("E:\\Average.xls")); Row row = workbook.getSheetAt(0).getRow(0); Cell cell = row.getCell(6); System.out.println("formula before modify:" + cell.getCellFormula()); cell.setCellFormula("B2:F1"); System.out.println("formula after modify:" + cell.getCellFormula()); } The Average.xls in the attached file.
Hi, I wonder if this is really a bug, and not an expected behavior in Office. When you input range "A2:F1", you mean "a smallest rectangle containing both A2 and F1 cells", and having such rectangle it is further referred by top-left and bottom-right corners - which in your case would be A1 and F2 (A2 and F1 which were provided are actually bottom-left and top-right corners). To confirm that I just tried to input range "A2:F1" directly in LibreOffice and formula was switched to "A1:F2" also.