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: [email protected]
For additional commands, e-mail: [email protected]
