From 5874f55dcc189ffdff5e0bbef7be5cf54508dafe Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Mon, 25 Nov 2024 21:09:54 +0000 Subject: [PATCH] add mirr test git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1922096 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/functions/Mirr.java | 6 ++-- .../poi/ss/formula/functions/TestMirr.java | 35 +++++++++++++++++++ 2 files changed, 38 insertions(+), 3 deletions(-) diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java index ae4871ee78..e72f2ebf0e 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java @@ -56,10 +56,10 @@ public class Mirr extends MultiOperandNumericFunction { @Override protected double evaluate(double[] values) throws EvaluationException { - double financeRate = values[values.length-2]; - double reinvestRate = values[values.length-1]; + final double financeRate = values[values.length-2]; + final double reinvestRate = values[values.length-1]; - double[] mirrValues = Arrays.copyOf(values, values.length - 2); + final double[] mirrValues = Arrays.copyOf(values, values.length - 2); boolean mirrValuesAreAllNegatives = true; boolean mirrValuesAreAllPositives = true; diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java index 0899e88907..69d9aa825d 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java @@ -112,6 +112,41 @@ final class TestMirr { assertEquals(0.18736225093, res, 0.00000001); } + @Test + void testMicrosoftSample() { + // https://support.microsoft.com/en-us/office/mirr-function-b020f038-7492-4fb4-93c1-35c345b53524 + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + + int row = 0; + sheet.createRow(row++).createCell(0).setCellValue("Data"); + sheet.createRow(row++).createCell(0).setCellValue(-120000); + sheet.createRow(row++).createCell(0).setCellValue(39000); + sheet.createRow(row++).createCell(0).setCellValue(30000); + sheet.createRow(row++).createCell(0).setCellValue(21000); + sheet.createRow(row++).createCell(0).setCellValue(37000); + sheet.createRow(row++).createCell(0).setCellValue(46000); + sheet.createRow(row++).createCell(0).setCellValue(0.1); + sheet.createRow(row++).createCell(0).setCellValue(0.12); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = sheet.createRow(row).createCell(0); + cell.setCellFormula("MIRR(A2:A7, A8, A9)"); + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + assertEquals(0.126094, cell.getNumericCellValue(), 0.00000015); + + cell.setCellFormula("MIRR(A2:A5, A8, A9)"); + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + assertEquals(-0.048044655, cell.getNumericCellValue(), 0.00000015); + + cell.setCellFormula("MIRR(A2:A7, A8, .14)"); + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + assertEquals(0.134759111, cell.getNumericCellValue(), 0.00000015); + } + @Test void testMirrFromSpreadsheet() { HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("mirrTest.xls"); -- 2.39.5