https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #2 from Yegor Kozlov <[email protected]> 2010-12-09 05:07:16 EST --- There were a few problems with the code as originally submitted: (1) Irr incorectly ineterpeted columns and rows. Look at your test case, you populate cells A1:E1, but evaluate A1:A5 + public void testEvaluateInSheet() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + HSSFRow row = sheet.createRow(0); + + row.createCell(0).setCellValue(-4000d); + row.createCell(1).setCellValue(1200d); + row.createCell(2).setCellValue(1410d); + row.createCell(3).setCellValue(1875d); + row.createCell(4).setCellValue(1050d); + + HSSFCell cell = row.createCell(5); + cell.setCellFormula("IRR(A1:A5)"); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + double res = cell.getNumericCellValue(); + assertEquals(0.143d, Math.round(res * 1000d) / 1000d); + } (2) Avoid implicit casting to TwoDEval or NumberEval. Input arguments can be numbers, strings, booleans, arrays, named ranges or formula results: =NPV(10%,A2:A6) =NPV(C2,A2:A6) // where C2=10% =NPV(C2,A2:A6) // where C2=TRUE which evaluates to 1! =NPV(named_range1,named_range1) =NPV(COUNT(A1,A2),INDIRECT("A2:A6")) =NPV(COUNT(A1,A2),{100, 200, 300}) The best candidate to convert input ValueEvals into an array of doubles is AggregateFunction.ValueCollector, I raised visibility of this class from private to default and changed Irr and Npv to use it. (3) The implementation of Irr does not properly work for short data series and a negative guess. I created a sample .xls file based on the data from Excel online documentation and the calculated bresult for =IRR(A2:A4,-10%) is wrong. See failing TestIrr#testIrrFromSpreadsheet(). Row[14]: IRR(A2:A4,-10%) expected:<-0.443506941334654> but was:<-25833.516857587092> Row[15]: IRR(A2:A4,irr_guess) expected:<-0.44350694133474056> but was:<-100630.10400193676> Please take a look at the changes. The updated patch includes both Irr nd Npv implementations. Regards, Yegor -- Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
