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 19:28:34 UTC
svn commit: r1892090 - 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 19:28:33 2021
New Revision: 1892090
URL: http://svn.apache.org/viewvc?rev=1892090&view=rev
Log:
[bug-49202] add PERCENTRANK.EXC function
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/PercentRank.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java
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/PercentRankExcFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java?rev=1892090&r1=1892089&r2=1892090&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankExcFunction.java Sat Aug 7 19:28:33 2021
@@ -135,13 +135,13 @@ final class PercentRankExcFunction imple
if (d < x) lessThanCount++;
}
BigDecimal result = new BigDecimal((double)(lessThanCount + 1) / (double)(numbers.size() + 1));
- return new NumberEval(PercentRank.round(result, significance, RoundingMode.DOWN));
+ return new NumberEval(PercentRank.round(result, significance));
} else {
- ValueEval belowRank = calculateRank(numbers, closestMatchBelow, significance, false);
+ ValueEval belowRank = calculateRank(numbers, closestMatchBelow, significance + 3, false);
if (!(belowRank instanceof NumberEval)) {
return belowRank;
}
- ValueEval aboveRank = calculateRank(numbers, closestMatchAbove, significance, false);
+ ValueEval aboveRank = calculateRank(numbers, closestMatchAbove, significance + 3, false);
if (!(aboveRank instanceof NumberEval)) {
return aboveRank;
}
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=1892090&r1=1892089&r2=1892090&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 19:28:33 2021
@@ -18,6 +18,7 @@
package org.apache.poi.ss.formula.functions;
import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.util.Internal;
import java.math.BigDecimal;
@@ -123,13 +124,13 @@ public final class PercentRank implement
return ErrorEval.NA;
}
BigDecimal result = new BigDecimal((double)lessThanCount / (double)(lessThanCount + greaterThanCount));
- return new NumberEval(round(result, significance, RoundingMode.DOWN));
+ return new NumberEval(round(result, significance));
} else {
- ValueEval belowRank = calculateRank(numbers, closestMatchBelow, significance, false);
+ ValueEval belowRank = calculateRank(numbers, closestMatchBelow, significance + 3, false);
if (!(belowRank instanceof NumberEval)) {
return belowRank;
}
- ValueEval aboveRank = calculateRank(numbers, closestMatchAbove, significance, false);
+ ValueEval aboveRank = calculateRank(numbers, closestMatchAbove, significance + 3, false);
if (!(aboveRank instanceof NumberEval)) {
return aboveRank;
}
@@ -142,16 +143,17 @@ public final class PercentRank implement
NumberEval belowRank, NumberEval aboveRank, int significance) {
double diff = closestMatchAbove - closestMatchBelow;
double pos = x - closestMatchBelow;
- double rankDiff = aboveRank.getNumberValue() - belowRank.getNumberValue();
- BigDecimal result = new BigDecimal(belowRank.getNumberValue() + (rankDiff * (pos / diff)));
- return new NumberEval(round(result, significance, RoundingMode.HALF_UP));
+ BigDecimal rankDiff = new BigDecimal(NumberToTextConverter.toText(aboveRank.getNumberValue() - belowRank.getNumberValue()));
+ BigDecimal result = new BigDecimal(belowRank.getNumberValue()).add(rankDiff.multiply(new BigDecimal(pos / diff)));
+ return new NumberEval(round(result, significance));
}
@Internal
- public static double round(BigDecimal bd, int significance, RoundingMode rounding) {
+ public static double round(BigDecimal bd, int significance) {
//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();
+ BigDecimal bd2 = bd.setScale(significance + 3, RoundingMode.HALF_UP);
+ return bd2.setScale(significance, RoundingMode.DOWN).doubleValue();
}
@Internal
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java?rev=1892090&r1=1892089&r2=1892090&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankExcFunction.java Sat Aug 7 19:28:33 2021
@@ -38,7 +38,7 @@ public class TestPercentRankExcFunction
// PERCENTRANK.INC test case (for comparison)
@Test
- void testMicrosoftExample1() throws IOException {
+ void testPercentRankIncExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
@@ -53,6 +53,18 @@ public class TestPercentRankExcFunction
}
}
+ //https://support.microsoft.com/en-us/office/percentrank-exc-function-d8afee96-b7e2-4a2f-8c01-8fcdedaa6314
+ @Test
+ void testMicrosoftExample1() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook2()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A10, 7)", 0.7);
+ assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A10,5.43)", 0.381);
+ assertDouble(fe, cell, "PERCENTRANK.EXC(A2:A10,5.43,1)", 0.3);
+ }
+ }
+
@Test
void testErrorCases() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
@@ -82,6 +94,22 @@ public class TestPercentRankExcFunction
return wb;
}
+ private HSSFWorkbook initWorkbook2() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Data");
+ addRow(sheet, 1, 1);
+ addRow(sheet, 2, 2);
+ addRow(sheet, 3, 3);
+ addRow(sheet, 4, 6);
+ addRow(sheet, 5, 6);
+ addRow(sheet, 6, 6);
+ addRow(sheet, 7, 7);
+ addRow(sheet, 8, 8);
+ addRow(sheet, 9, 9);
+ return wb;
+ }
+
private static void confirmErrorResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedResult) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
Modified: 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/TestPercentRankIncFunction.java?rev=1892090&r1=1892089&r2=1892090&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/TestPercentRankIncFunction.java Sat Aug 7 19:28:33 2021
@@ -48,6 +48,7 @@ public class TestPercentRankIncFunction
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.INC(A2:A11,5,5)", 0.58333);
assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,1)", 0);
assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,13)", 1);
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org