[bug-49202] add PERCENTRANK.EXC function

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

5 files changed, 262 insertions, 3 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
index 6eb2c8b8bc..ca0a91df00 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
@@ -155,6 +155,7 @@ public final class AnalysisToolPak implements UDFFinder {
         r(m, "ODDLPRICE", null);
         r(m, "ODDLYIELD", null);
         r(m, "PRICE", null);
+        r(m, "PERCENTRANK.EXC", PercentRankExcFunction.instance);
         r(m, "PERCENTRANK.INC", PercentRankIncFunction.instance);
         r(m, "PRICEDISC", null);
         r(m, "PRICEMAT", null);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java
new file mode 100644
index 0000000000..bc4d887245
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java
@@ -0,0 +1,162 @@
+/* ====================================================================
+   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.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.functions.PercentRank;
+
+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.EXC()
+ *
+ * <b>Syntax</b>:<br>
+ * <b>PERCENTRANK.EXC</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>  Optional. A value that identifies the number of significant digits for the returned percentage value.
+ * If omitted, PERCENTRANK.EXC uses three digits (0.xxx).<br>
+ * <br>
+ * Returns a number between 0 and 1 (exclusive) representing a percentage. PERCENTRANK.INC returns value between 0 and 1 (inclusive).
+ *
+ * @see PercentRank
+ * @see PercentRankIncFunction
+ * @since POI 5.0.1
+ */
+final class PercentRankExcFunction implements FreeRefFunction {
+
+    public static final FreeRefFunction instance = new PercentRankExcFunction(ArgumentsEvaluator.instance);
+
+    private ArgumentsEvaluator evaluator;
+
+    private PercentRankExcFunction(ArgumentsEvaluator anEvaluator) {
+        // enforces singleton
+        this.evaluator = anEvaluator;
+    }
+
+    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+        return evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+    }
+
+    private 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 = PercentRank.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);
+                if (significance < 1) {
+                    return ErrorEval.NUM_ERROR;
+                }
+            } 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;
+        double min = Double.MAX_VALUE;
+        double max = Double.MIN_VALUE;
+        if (recurse) {
+            for (Double d : numbers) {
+                if (d <= x && d > closestMatchBelow) closestMatchBelow = d;
+                if (d > x && d < closestMatchAbove) closestMatchAbove = d;
+                if (d < min) min = d;
+                if (d > max) max = d;
+            }
+            if (x < min || x > max) {
+                return ErrorEval.NA;
+            }
+        }
+        if (!recurse || closestMatchBelow == x || closestMatchAbove == x) {
+            int lessThanCount = 0;
+            int greaterThanCount = 0;
+            int matchesCount = 0;
+            for (Double d : numbers) {
+                if (d < x) lessThanCount++;
+                else if (d > x) greaterThanCount++;
+                else matchesCount++;
+            }
+            BigDecimal result = new BigDecimal((double)(lessThanCount + 1) / (double)(numbers.size() + 1));
+            return new NumberEval(PercentRank.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(PercentRank.round(result, significance, RoundingMode.HALF_UP));
+        }
+    }
+}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
index 878b2ca774..bcb0f65dde 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
@@ -33,9 +33,11 @@ import org.apache.poi.ss.formula.functions.PercentRank;
  * <b>significance</b>  Optional. A value that identifies the number of significant digits for the returned percentage value.
  * If omitted, PERCENTRANK.INC uses three digits (0.xxx).<br>
  * <br>
- * Returns a number between 0 and 1 representing a percentage.
+ * Returns a number between 0 and 1 representing a percentage. PERCENTRANK.INC gives same result as PERCENTRANK
+ * with min value having a result of 0 and max has a result of 1. PERCENTRANK.EXC returns value between 0 and 1 (exclusive).
  *
  * @see PercentRank
+ * @see PercentRankExcFunction
  * @since POI 5.0.1
  */
 final class PercentRankIncFunction implements FreeRefFunction {
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
index 8fac522a78..0eecb75c07 100644
--- 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
@@ -18,6 +18,7 @@
 package org.apache.poi.ss.formula.functions;
 
 import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.util.Internal;
 
 import java.math.BigDecimal;
 import java.math.RoundingMode;
@@ -142,13 +143,15 @@ public final class PercentRank implements Function {
         }
     }
 
-    private double round(BigDecimal bd, int significance, RoundingMode rounding) {
+    @Internal
+    public static 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 {
+    @Internal
+    public static List<ValueEval> getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
         if (eval instanceof AreaEval) {
             AreaEval ae = (AreaEval)eval;
             List<ValueEval> list = new ArrayList<>();
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java
new file mode 100644
index 0000000000..10297de357
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java
@@ -0,0 +1,91 @@
+
+/* ====================================================================
+   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.atp;
+
+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.CellValue;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+import static org.apache.poi.ss.util.Utils.addRow;
+import static org.apache.poi.ss.util.Utils.assertDouble;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Testcase for function PERCENTRANK.EXC()
+ */
+public class TestPercentRankExcFunction {
+
+    // PERCENTRANK.INC test case (for comparison)
+    @Test
+    void testMicrosoftExample1() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook1()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,1)", 0.09);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,13)", 0.909);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,2)", 0.363);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,4)", 0.545);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,8)", 0.636);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,8,2)", 0.63);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,8,4)", 0.6363);
+            assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A11,5)", 0.568);
+        }
+    }
+
+    @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.EXC(A2:A11,0)", FormulaError.NA);
+            confirmErrorResult(fe, cell, "PERCENTRANK.EXC(A2:A11,100)", FormulaError.NA);
+            confirmErrorResult(fe, cell, "PERCENTRANK.EXC(B2:B11,100)", FormulaError.NUM);
+            confirmErrorResult(fe, cell, "PERCENTRANK.EXC(A2:A11,8,0)", 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 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());
+    }
+}