You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/08/07 15:33:35 UTC
svn commit: r1892084 - in /poi/trunk/poi/src:
main/java/org/apache/poi/ss/formula/atp/
main/java/org/apache/poi/ss/formula/functions/
test/java/org/apache/poi/ss/formula/atp/
Author: fanningpj
Date: Sat Aug 7 15:33:35 2021
New Revision: 1892084
URL: http://svn.apache.org/viewvc?rev=1892084&view=rev
Log:
[bug-49202] add PERCENTRANK.EXC function
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java
- copied, changed from r1892081, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java
- copied, changed from r1892082, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1892084&r1=1892083&r2=1892084&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Sat Aug 7 15:33:35 2021
@@ -155,6 +155,7 @@ public final class AnalysisToolPak imple
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);
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java (from r1892081, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java&r1=1892081&r2=1892084&rev=1892084&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java Sat Aug 7 15:33:35 2021
@@ -22,34 +22,141 @@ 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.INC()
+ * Implementation of 'Analysis Toolpak' the Excel function PERCENTRANK.EXC()
*
* <b>Syntax</b>:<br>
- * <b>PERCENTRANK.INC</b>(<b>array</b>, <b>X</b>, <b>[significance]</b>)<p>
+ * <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.INC uses three digits (0.xxx).<br>
+ * If omitted, PERCENTRANK.EXC uses three digits (0.xxx).<br>
* <br>
- * Returns a number between 0 and 1 representing a percentage.
+ * 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 PercentRankIncFunction implements FreeRefFunction {
+final class PercentRankExcFunction implements FreeRefFunction {
- public static final FreeRefFunction instance = new PercentRankIncFunction(ArgumentsEvaluator.instance);
+ public static final FreeRefFunction instance = new PercentRankExcFunction(ArgumentsEvaluator.instance);
private ArgumentsEvaluator evaluator;
- private PercentRankIncFunction(ArgumentsEvaluator anEvaluator) {
+ private PercentRankExcFunction(ArgumentsEvaluator anEvaluator) {
// enforces singleton
this.evaluator = anEvaluator;
}
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
- return PercentRank.instance.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ 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));
+ }
}
}
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java?rev=1892084&r1=1892083&r2=1892084&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java Sat Aug 7 15:33:35 2021
@@ -33,9 +33,11 @@ import org.apache.poi.ss.formula.functio
* <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 {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java?rev=1892084&r1=1892083&r2=1892084&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java Sat Aug 7 15:33:35 2021
@@ -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 implement
}
}
- 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<>();
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java (from r1892082, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java&r1=1892082&r2=1892084&rev=1892084&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java Sat Aug 7 15:33:35 2021
@@ -32,24 +32,24 @@ import static org.apache.poi.ss.util.Uti
import static org.junit.jupiter.api.Assertions.assertEquals;
/**
- * Testcase for function PERCENTRANK.INC()
+ * Testcase for function PERCENTRANK.EXC()
*/
-public class TestPercentRankIncFunction {
+public class TestPercentRankExcFunction {
- //https://support.microsoft.com/en-us/office/percentrank-inc-function-149592c9-00c0-49ba-86c1-c1f45b80463a
+ // 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.INC(A2:A11,2)", 0.333);
- assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,4)", 0.555);
- assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8)", 0.666);
- assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8,2)", 0.66);
- assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8,4)", 0.6666);
- assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,5)", 0.583);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,1)", 0);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,13)", 1);
+ 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);
}
}
@@ -58,10 +58,10 @@ public class TestPercentRankIncFunction
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,0)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,100)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK.INC(B2:B11,100)", FormulaError.NUM);
- confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,8,0)", FormulaError.NUM);
+ 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);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org