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 18:36:13 UTC
svn commit: r1892088 - in
/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula:
atp/PercentRankExcFunction.java functions/PercentRank.java
Author: fanningpj
Date: Sat Aug 7 18:36:12 2021
New Revision: 1892088
URL: http://svn.apache.org/viewvc?rev=1892088&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
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=1892088&r1=1892087&r2=1892088&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 18:36:12 2021
@@ -25,7 +25,6 @@ import org.apache.poi.ss.formula.functio
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
-import java.util.Collections;
import java.util.List;
/**
@@ -132,12 +131,8 @@ final class PercentRankExcFunction imple
}
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));
@@ -150,13 +145,8 @@ final class PercentRankExcFunction imple
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));
+ return PercentRank.interpolate(x, closestMatchBelow, closestMatchAbove,
+ (NumberEval)belowRank, (NumberEval)aboveRank, significance);
}
}
}
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=1892088&r1=1892087&r2=1892088&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 18:36:12 2021
@@ -133,17 +133,21 @@ public final class PercentRank implement
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));
+ return interpolate(x, closestMatchBelow, closestMatchAbove, (NumberEval)belowRank, (NumberEval)aboveRank, significance);
}
}
@Internal
+ public static NumberEval interpolate(double x, double closestMatchBelow, double closestMatchAbove,
+ 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));
+ }
+
+ @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
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org