After filling a couple of rows with 5 columns of data I'm trying to create the following formula that works fine in Excel: HSSFRow r = sheet.createRow(0); // create the IRR formula short col = 0; HSSFCell c = r.createCell(col++); c.setCellType(HSSFCell.CELL_TYPE_FORMULA); c.setCellFormula("(1+IRR(SUMIF(A:A;ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A)));B:B);0))^365-1"); This results in the following inlikely error: Exception in thread "main" java.lang.IllegalArgumentException: Invalid Formula cell reference: 'A' at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:48) at org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:43) at org.apache.poi.hssf.record.formula.AreaPtg.<init>(AreaPtg.java:55) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:290) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660) at org.apache.poi.hssf.model.FormulaParser.Add(FormulaParser.java:674) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:710) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:518) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:812) at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:649) Is there any way this formula could be created from POI?
I've verified error message with 3.1b1- still existing but different: Exception in thread "main" org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Integer Expected at org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:137) at org.apache.poi.hssf.model.FormulaParser.parseNumber(FormulaParser.java:578) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:545) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:479) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:326) at org.apache.poi.hssf.model.FormulaParser.parseIdent(FormulaParser.java:236) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:542) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:479) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:326) at org.apache.poi.hssf.model.FormulaParser.parseIdent(FormulaParser.java:236) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:542) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:787) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:532) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:820) at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:641)
Based on the 3.1b1 error message I've also investigated if POI was potentially confused by the A:A column notation and changed the formula like this, unfortunately still with the same error message: (1+IRR(SUMIF(A1:A100;ROW(INDIRECT(MIN(A1:A100)&\":\"&MAX(A1:A100)));B1:B100);0))^365-1 I could find if this should work- may be the "Everything else" under unsupported features on the POI website includes my case :(
The problem appears to be with the use of semicolons (;) for argument separators when POI expects commas (,). My understanding was that Excel uses commas too. Is there any circumstance that allows semicolon? If I change the semicolons to commas, POI parses the formula OK. I have added a patch (in svn r658033) to improve the formula parse error messages. The first code example in this bugzilla should now produce this: Exception in thread "main" org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Parse error near char 16';' in specified formula '(1+IRR(SUMIF(A:A;ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)));B:B);0))^365-1'. Expected Integer at org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:140) at ... BTW - The full column references should not be a problem for this example. A partial fix for bug 44410 was applied in svn r628044 (rel 3.1-beta1), and this seems to be enough to make expressions like 'A:A' work.
Can confirm ; was the problem, thank you. My mistake- copied formula our of English Excel on English XP with German local settings... Should the error message potentially say "Expected integer or ;"?
Update- there is a second bug lurking behind the first one. Let me know if I should open separately: If you enter two sample lines of data into columns a and b like this: 01.01.2007 100,00 01.01.2008 -250 In Excel however I see either #VALUE! or #DIV/0! in the formula field. Entering the field, appending a " " character and OKing it fixes the problem and field displays a value. It seems Excel is correcting the formula in the background- visually it does not change by this process.
Bug 45060 was opened to deal with the additional issue mentioned in comment #5. As it turns out after fixing that bug, there is nothing else stopping the example in this bugzilla from working.