on evaluation of this formala NPER(12,4500,100000.0,100000.0) (this formula contain VALUE error ) POI give NumericCellValue = 15 Cell Formula is : NPER(12,4500,100000.0,100000.0) Cell RichStringValue is : Cell NumericCellValue is : 15.0
It looks like POI calculates NPER OK. Excel gives a result of #NUM! for the example parameters, and so does POI. FinanceLib.nper has not changed in a long time (2005), so recent versions of POI should be OK too. You didn't attach any example code, but I am guessing that you were using the method HSSFFormulaEvaluator.evaluateFormulaCell(), and calling HSSFCell.getNumericCellValue() to check the result. Prior to svn r694631 POI did not write error result codes back to the cached formula value, so this could explain your observations. A new test case (TestNper.testEvaluate_bug_45732) has been checked in (svn 696109) which produces behaviour like you have described. Note also, since svn r694631 a new method has become available: HSSFCell.getCachedFormulaResultType(), which can take the trial and error out of determining which getter method value type to use when reading formula cell cached values.