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 2022/05/26 10:53:56 UTC
svn commit: r1901279 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/eval/ main/java/org/apache/poi/ss/formula/functions/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Thu May 26 10:53:56 2022
New Revision: 1901279
URL: http://svn.apache.org/viewvc?rev=1901279&view=rev
Log:
add STDEVP function support
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java
- copied, changed from r1901277, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/StatsLib.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1901279&r1=1901278&r2=1901279&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java Thu May 26 10:53:56 2022
@@ -204,6 +204,7 @@ public final class FunctionEval {
retval[189] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DPRODUCT);
retval[190] = LogicalFunction.ISNONTEXT;
+ retval[193] = AggregateFunction.STDEVP;
retval[194] = AggregateFunction.VARP;
// 195: DSTDEVP
// 196: DVARP
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java?rev=1901279&r1=1901278&r2=1901279&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java Thu May 26 10:53:56 2022
@@ -228,6 +228,14 @@ public abstract class AggregateFunction
return StatsLib.stdev(values);
}
};
+ public static final Function STDEVP = new AggregateFunction() {
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.stdevp(values);
+ }
+ };
public static final Function SUM = new AggregateFunction() {
protected double evaluate(double[] values) {
return MathX.sum(values);
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/StatsLib.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/StatsLib.java?rev=1901279&r1=1901278&r2=1901279&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/StatsLib.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/StatsLib.java Thu May 26 10:53:56 2022
@@ -57,6 +57,14 @@ final class StatsLib {
return r;
}
+ public static double stdevp(double[] v) {
+ double r = Double.NaN;
+ if (v!=null && v.length > 1) {
+ r = Math.sqrt( devsq(v) / v.length );
+ }
+ return r;
+ }
+
public static double var(double[] v) {
double r = Double.NaN;
if (v!=null && v.length > 1) {
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java (from r1901277, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java&r1=1901277&r2=1901279&rev=1901279&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java Thu May 26 10:53:56 2022
@@ -29,49 +29,36 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for function DPRODUCT()
+ * Testcase for function STDEVP()
*/
-public class TestDProduct {
+public class TestStdevp {
- //https://support.microsoft.com/en-us/office/dproduct-function-4f96b13e-d49c-47a7-b769-22f6d017cb31
+ //https://support.microsoft.com/en-us/office/stdevp-function-1f7c1c88-1bec-4422-8242-e9f7dc8bb195
@Test
void testMicrosoftExample1() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(false)) {
+ try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
- assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:F3)", 800, 0.0000000001);
+ assertDouble(fe, cell, "STDEVP(A3:A12)", 26.0545581424825, 0.00000000001);
+ assertDouble(fe, cell, "STDEV(A3:A12)", 27.4639157198435, 0.00000000001);
}
}
- @Test
- void testNoMatch() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(true)) {
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
- assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:A2)", 0);
- assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:A3)", 604800);
- }
- }
-
- private HSSFWorkbook initWorkbook1(boolean noMatch) {
+ private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "Tree", "Height", "Age", "Yield", "Profit", "Height");
- if (noMatch) {
- addRow(sheet, 1, "=NoMatch");
- addRow(sheet, 2);
- } else {
- addRow(sheet, 1, "=Apple", ">10", null, null, null, "<16");
- addRow(sheet, 2, "=Pear");
- }
- addRow(sheet, 3);
- addRow(sheet, 4, "Tree", "Height", "Age", "Yield", "Profit");
- addRow(sheet, 5, "Apple", 18, 20, 14, 105);
- addRow(sheet, 6, "Pear", 12, 12, 10, 96);
- addRow(sheet, 7, "Cherry", 13, 14, 9, 105);
- addRow(sheet, 8, "Apple", 14, 15, 10, 75);
- addRow(sheet, 9, "Pear", 9, 8, 8, 77);
- addRow(sheet, 10, "Apple", 8, 9, 6, 45);
+ addRow(sheet, 0, "Data");
+ addRow(sheet, 1, "Strength");
+ addRow(sheet, 2, 1345);
+ addRow(sheet, 3, 1301);
+ addRow(sheet, 4, 1368);
+ addRow(sheet, 5, 1322);
+ addRow(sheet, 6, 1310);
+ addRow(sheet, 7, 1370);
+ addRow(sheet, 8, 1318);
+ addRow(sheet, 9, 1350);
+ addRow(sheet, 10, 1303);
+ addRow(sheet, 11, 1299);
return wb;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org