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 11:07:41 UTC
svn commit: r1901280 - 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 11:07:41 2022
New Revision: 1901280
URL: http://svn.apache.org/viewvc?rev=1901280&view=rev
Log:
support DVARP and DSTDEVP
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java
- copied, changed from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java
- copied, changed from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.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/DStarRunner.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.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=1901280&r1=1901279&r2=1901280&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 11:07:41 2022
@@ -206,8 +206,8 @@ public final class FunctionEval {
retval[193] = AggregateFunction.STDEVP;
retval[194] = AggregateFunction.VARP;
- // 195: DSTDEVP
- // 196: DVARP
+ retval[195] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DSTDEVP);
+ retval[196] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DVARP);
retval[197] = NumericFunction.TRUNC;
retval[198] = LogicalFunction.ISLOGICAL;
// 199: DCOUNTA
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java?rev=1901280&r1=1901279&r2=1901280&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java Thu May 26 11:07:41 2022
@@ -63,8 +63,12 @@ public final class DStarRunner implement
DAVERAGE(DAverage::new),
/** @see DStdev */
DSTDEV(DStdev::new),
+ /** @see DStdevp */
+ DSTDEVP(DStdevp::new),
/** @see DVar */
DVAR(DVar::new),
+ /** @see DVarp */
+ DVARP(DVarp::new),
/** @see DProduct */
DPRODUCT(DProduct::new),
;
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java (from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java&r1=1901275&r2=1901280&rev=1901280&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java Thu May 26 11:07:41 2022
@@ -26,10 +26,10 @@ import java.math.BigDecimal;
import java.util.ArrayList;
/**
- * Implementation of the DStdev function:
+ * Implementation of the DStdevp function:
* Gets the standard deviation value of a column in an area with given conditions.
*/
-public final class DStdev implements IDStarAlgorithm {
+public final class DStdevp implements IDStarAlgorithm {
private final ArrayList<NumericValueEval> values = new ArrayList<>();
@Override
@@ -47,7 +47,7 @@ public final class DStdev implements IDS
for (NumericValueEval d : values) {
array[pos++] = d.getNumberValue();
}
- final double stdev = StatsLib.stdev(array);
+ final double stdev = StatsLib.stdevp(array);
return new NumberEval(new BigDecimal(NumberToTextConverter.toText(stdev)).doubleValue());
}
}
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java (from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java&r1=1901275&r2=1901280&rev=1901280&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java Thu May 26 11:07:41 2022
@@ -26,10 +26,10 @@ import java.math.BigDecimal;
import java.util.ArrayList;
/**
- * Implementation of the DVar function:
+ * Implementation of the DVarp function:
* Gets the variance value of a column in an area with given conditions.
*/
-public final class DVar implements IDStarAlgorithm {
+public final class DVarp implements IDStarAlgorithm {
private final ArrayList<NumericValueEval> values = new ArrayList<>();
@Override
@@ -47,7 +47,7 @@ public final class DVar implements IDSta
for (NumericValueEval d : values) {
array[pos++] = d.getNumberValue();
}
- final double var = StatsLib.var(array);
+ final double var = StatsLib.varp(array);
return new NumberEval(new BigDecimal(NumberToTextConverter.toText(var)).doubleValue());
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java?rev=1901280&r1=1901279&r2=1901280&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java Thu May 26 11:07:41 2022
@@ -29,7 +29,7 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for function DSTDEV()
+ * Testcase for function DSTDEV() and DSTDEVP()
*/
public class TestDStdev {
@@ -43,6 +43,16 @@ public class TestDStdev {
}
}
+ //https://support.microsoft.com/en-us/office/dstdevp-function-04b78995-da03-4813-bbd9-d74fd0f5d94b
+ @Test
+ void testDSTDEVPMicrosoftExample1() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
+ assertDouble(fe, cell, "DSTDEVP(A5:E11, \"Yield\", A1:A3)", 2.65329983228432, 0.0000000001);
+ }
+ }
+
private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java?rev=1901280&r1=1901279&r2=1901280&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java Thu May 26 11:07:41 2022
@@ -29,7 +29,7 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for function DVAR()
+ * Testcase for function DVAR() and DVARP()
*/
public class TestDVar {
@@ -43,6 +43,16 @@ public class TestDVar {
}
}
+ //https://support.microsoft.com/en-us/office/dvarp-function-eb0ba387-9cb7-45c8-81e9-0394912502fc
+ @Test
+ void testDVARPMicrosoftExample1() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
+ assertDouble(fe, cell, "DVARP(A4:E10, \"Yield\", A1:A3)", 7.04, 0.0000000001);
+ }
+ }
+
private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org