[bug-49202] add PERCENTRANK function

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1892077 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 255 insertions, 1 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
index 7f3255b475..f0fc275844 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
@@ -309,7 +309,7 @@ public final class FunctionEval {
         retval[326] = AggregateFunction.SMALL;
         // 327: QUARTILE
         retval[328] = AggregateFunction.PERCENTILE;
-        // 329: PERCENTRANK
+        retval[329] = PercentRank.instance;
         retval[330] = new Mode();
         // 331: TRIMMEAN
         // 332: TINV
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java
new file mode 100644
index 0000000000..03a9ae54bc
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java
@@ -0,0 +1,160 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.*;
+
+import java.math.BigDecimal;
+import java.math.RoundingMode;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+/**
+ * Implementation of 'Analysis Toolpak' the Excel function PERCENTRANK()
+ *
+ * <b>Syntax</b>:<br>
+ * <b>PERCENTRANK</b>(<b>array</b>, <b>X</b>, <b>[significance]</b>)<p>
+ *
+ * <b>array</b>  The array or range of data with numeric values that defines relative standing.<br>
+ * <b>X</b>  The value for which you want to know the rank.<br>
+ * <b>significance</b>  TOptional. A value that identifies the number of significant digits for the returned percentage value.
+ * If omitted, PERCENTRANK uses three digits (0.xxx).<br>
+ * <br>
+ * Returns a number between 0 and 1 representing a percentage.
+ */
+public final class PercentRank implements Function {
+
+    public static final Function instance = new PercentRank();
+
+    private PercentRank() {
+        // Enforce singleton
+    }
+
+    public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
+        if (args.length < 2) {
+            return ErrorEval.VALUE_INVALID;
+        }
+        double x;
+        try {
+            ValueEval ev = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
+            x = OperandResolver.coerceValueToDouble(ev);
+        } catch (EvaluationException e) {
+            ValueEval error = e.getErrorEval();
+            if (error == ErrorEval.NUM_ERROR) {
+                return error;
+            }
+            return ErrorEval.NUM_ERROR;
+        }
+
+        ArrayList<Double> numbers = new ArrayList<>();
+        try {
+            List<ValueEval> values = getValues(args[0], srcRowIndex, srcColumnIndex);
+            for (ValueEval ev : values) {
+                if (ev instanceof BlankEval || ev instanceof MissingArgEval) {
+                    //skip
+                } else {
+                    numbers.add(OperandResolver.coerceValueToDouble(ev));
+                }
+            }
+        } catch (EvaluationException e) {
+            ValueEval error = e.getErrorEval();
+            if (error != ErrorEval.NA) {
+                return error;
+            }
+            return ErrorEval.NUM_ERROR;
+        }
+
+        if (numbers.isEmpty()) {
+            return ErrorEval.NUM_ERROR;
+        }
+
+        int significance = 3;
+        if (args.length > 2) {
+            try {
+                ValueEval ev = OperandResolver.getSingleValue(args[2], srcRowIndex, srcColumnIndex);
+                significance = OperandResolver.coerceValueToInt(ev);
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            }
+        }
+
+        return calculateRank(numbers, x, significance, true);
+    }
+
+    private ValueEval calculateRank(List<Double> numbers, double x, int significance, boolean recurse) {
+        double closestMatchBelow = Double.MIN_VALUE;
+        double closestMatchAbove = Double.MAX_VALUE;
+        if (recurse) {
+            for (Double d : numbers) {
+                if (d <= x && d > closestMatchBelow) closestMatchBelow = d;
+                if (d > x && d < closestMatchAbove) closestMatchAbove = d;
+            }
+        }
+        if (!recurse || closestMatchBelow == x || closestMatchAbove == x) {
+            int lessThanCount = 0;
+            int greaterThanCount = 0;
+            for (Double d : numbers) {
+                if (d < x) lessThanCount++;
+                else if (d > x) greaterThanCount++;
+            }
+            if (greaterThanCount == numbers.size() || lessThanCount == numbers.size()) {
+                return ErrorEval.NA;
+            }
+            BigDecimal result = new BigDecimal((double)lessThanCount / (double)(lessThanCount + greaterThanCount));
+            return new NumberEval(round(result, significance, RoundingMode.DOWN));
+        } else {
+            ValueEval belowRank = calculateRank(numbers, closestMatchBelow, significance, false);
+            if (!(belowRank instanceof NumberEval)) {
+                return belowRank;
+            }
+            ValueEval aboveRank = calculateRank(numbers, closestMatchAbove, significance, false);
+            if (!(aboveRank instanceof NumberEval)) {
+                return aboveRank;
+            }
+            NumberEval below = (NumberEval)belowRank;
+            NumberEval above = (NumberEval)aboveRank;
+            double diff = closestMatchAbove - closestMatchBelow;
+            double pos = x - closestMatchBelow;
+            double rankDiff = above.getNumberValue() - below.getNumberValue();
+            BigDecimal result = new BigDecimal(below.getNumberValue() + (rankDiff * (pos / diff)));
+            return new NumberEval(round(result, significance, RoundingMode.HALF_UP));
+        }
+    }
+
+    private double round(BigDecimal bd, int significance, RoundingMode rounding) {
+        //the rounding in https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442
+        //is very inconsistent, this hodge podge of rounding modes is the only way to match Excel results
+        return bd.setScale(significance, rounding).doubleValue();
+    }
+
+    private List<ValueEval> getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
+        if (eval instanceof AreaEval) {
+            AreaEval ae = (AreaEval)eval;
+            List<ValueEval> list = new ArrayList<>();
+            for (int r = ae.getFirstRow(); r <= ae.getLastRow(); r++) {
+                for (int c = ae.getFirstColumn(); c <= ae.getLastColumn(); c++) {
+                    list.add(OperandResolver.getSingleValue(ae.getAbsoluteValue(r, c), r, c));
+                }
+            }
+            return list;
+        } else {
+            return Collections.singletonList(OperandResolver.getSingleValue(eval, srcRowIndex, srcColumnIndex));
+        }
+    }
+}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java
new file mode 100644
index 0000000000..6d96426dd9
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java
@@ -0,0 +1,94 @@
+
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.*;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+import static org.apache.poi.ss.util.Utils.addRow;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Testcase for function PERCENTRANK()
+ */
+public class TestPercentRank {
+
+    //https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442
+    @Test
+    void testMicrosoftExample1() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook1()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,2)", 0.333);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,4)", 0.555);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8)", 0.666);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,2)", 0.66);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,4)", 0.6666);
+            confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,5)", 0.583);
+        }
+    }
+
+    @Test
+    void testErrorCases() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook1()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,0)", FormulaError.NA);
+            confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,100)", FormulaError.NA);
+            confirmErrorResult(fe, cell, "PERCENTRANK(B2:B11,100)", FormulaError.NUM);
+        }
+    }
+
+    private HSSFWorkbook initWorkbook1() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0, "Data");
+        addRow(sheet, 1, 13);
+        addRow(sheet, 2, 12);
+        addRow(sheet, 3, 11);
+        addRow(sheet, 4, 8);
+        addRow(sheet, 5, 4);
+        addRow(sheet, 6, 3);
+        addRow(sheet, 7, 2);
+        addRow(sheet, 8, 1);
+        addRow(sheet, 9, 1);
+        addRow(sheet, 10, 1);
+        return wb;
+    }
+
+    private static void confirmNumericResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) {
+        cell.setCellFormula(formulaText);
+        fe.notifyUpdateCell(cell);
+        CellValue result = fe.evaluate(cell);
+        assertEquals(result.getCellType(), CellType.NUMERIC);
+        assertEquals(expectedResult, result.getNumberValue(), 0.0001);
+    }
+
+    private static void confirmErrorResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedResult) {
+        cell.setCellFormula(formulaText);
+        fe.notifyUpdateCell(cell);
+        CellValue result = fe.evaluate(cell);
+        assertEquals(expectedResult.getCode(), result.getErrorValue());
+    }
+}