Implemented GEOMEAN function. Thanks to gallonfizik. This closes #136

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1849042 13f79535-47bb-0310-9956-ffa450edef68

2 files changed, 135 insertions, 0 deletions

diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestGeomean.java b/src/testcases/org/apache/poi/ss/formula/functions/TestGeomean.java
new file mode 100644
index 0000000000..0eedbee7e1
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/formula/functions/TestGeomean.java
@@ -0,0 +1,112 @@
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.*;
+import org.junit.Test;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+/**
+ * From Excel documentation at https://support.office.com/en-us/article/geomean-function-db1ac48d-25a5-40a0-ab83-0b38980e40d5:
+ * 1. Arguments can either be numbers or names, arrays, or references that contain numbers.
+ * 2. Logical values and text representations of numbers that you type directly into the list of arguments are counted.
+ * 3. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
+ * 4. Arguments that are error values or text that cannot be translated into numbers cause errors.
+ * 5. If any data point ≤ 0, GEOMEAN returns the #NUM! error value.
+ *
+ * Remarks:
+ * Actually, 5. is not true. If an error is encountered before a 0 value, the error is returned.
+ */
+public class TestGeomean {
+    @Test
+    public void acceptanceTest() {
+        Function geomean = getInstance();
+
+        final ValueEval result = geomean.evaluate(new ValueEval[]{new NumberEval(2), new NumberEval(3)}, 0, 0);
+        verifyNumericResult(2.449489742783178, result);
+    }
+
+    @Test
+    public void booleansByValueAreCoerced() {
+        final ValueEval[] args = {BoolEval.TRUE};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        verifyNumericResult(1.0, result);
+    }
+
+    @Test
+    public void stringsByValueAreCoerced() {
+        final ValueEval[] args = {new StringEval("2")};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        verifyNumericResult(2.0, result);
+    }
+
+    @Test
+    public void nonCoerceableStringsByValueCauseValueInvalid() {
+        final ValueEval[] args = {new StringEval("foo")};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        assertEquals(ErrorEval.VALUE_INVALID, result);
+    }
+
+    @Test
+    public void booleansByReferenceAreSkipped() {
+        final ValueEval[] args = new ValueEval[]{new NumberEval(2.0), EvalFactory.createRefEval("A1", BoolEval.TRUE)};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        verifyNumericResult(2.0, result);
+    }
+
+    @Test
+    public void booleansStringsAndBlanksByReferenceAreSkipped() {
+        ValueEval ref = EvalFactory.createAreaEval("A1:A3", new ValueEval[]{new StringEval("foo"), BoolEval.FALSE, BlankEval.instance});
+        final ValueEval[] args = {ref, new NumberEval(2.0)};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        verifyNumericResult(2.0, result);
+    }
+
+    @Test
+    public void stringsByValueAreCounted() {
+        final ValueEval[] args = {new StringEval("2.0")};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        verifyNumericResult(2.0, result);
+    }
+
+    @Test
+    public void missingArgCountAsZero() {
+        // and, naturally, produces a NUM_ERROR
+        final ValueEval[] args = {new NumberEval(1.0), MissingArgEval.instance};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        assertEquals(ErrorEval.NUM_ERROR, result);
+    }
+
+    /**
+     * Implementation-specific: the math lib returns 0 for the input [1.0, 0.0], but a NUM_ERROR should be returned.
+     */
+    @Test
+    public void sequence_1_0_shouldReturnError() {
+        final ValueEval[] args = {new NumberEval(1.0), new NumberEval(0)};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        assertEquals(ErrorEval.NUM_ERROR, result);
+    }
+
+    @Test
+    public void minusOneShouldReturnError() {
+        final ValueEval[] args = {new NumberEval(1.0), new NumberEval(-1.0)};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        assertEquals(ErrorEval.NUM_ERROR, result);
+    }
+
+    @Test
+    public void firstErrorPropagates() {
+        final ValueEval[] args = {ErrorEval.DIV_ZERO, ErrorEval.NUM_ERROR};
+        final ValueEval result = getInstance().evaluate(args, 0, 0);
+        assertEquals(ErrorEval.DIV_ZERO, result);
+    }
+
+    private void verifyNumericResult(double expected, ValueEval result) {
+        assertTrue(result instanceof NumberEval);
+        assertEquals(expected, ((NumberEval) result).getNumberValue(), 1e-15);
+    }
+
+    private Function getInstance() {
+        return AggregateFunction.GEOMEAN;
+    }
+}
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMultiOperandNumericFunction.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMultiOperandNumericFunction.java
index 31490a0547..a54a69f85d 100644
--- a/src/testcases/org/apache/poi/ss/formula/functions/TestMultiOperandNumericFunction.java
+++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMultiOperandNumericFunction.java
@@ -18,9 +18,13 @@
 package org.apache.poi.ss.formula.functions;
 
 import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
 
 import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.MissingArgEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
 import org.junit.Test;
 
 public class TestMultiOperandNumericFunction {
@@ -36,4 +40,23 @@ public class TestMultiOperandNumericFunction {
         };
         assertEquals(SpreadsheetVersion.EXCEL2007.getMaxFunctionArgs(), fun.getMaxNumOperands());
     }
+
+    @Test
+    public void missingArgEvalsAreCountedAsZero() {
+        MultiOperandNumericFunction instance = new Stub(true, true);
+        ValueEval result = instance.evaluate(new ValueEval[]{MissingArgEval.instance}, 0, 0);
+        assertTrue(result instanceof NumberEval);
+        assertEquals(0.0, ((NumberEval)result).getNumberValue(), 0);
+    }
+
+    private static class Stub extends MultiOperandNumericFunction {
+        protected Stub(boolean isReferenceBoolCounted, boolean isBlankCounted) {
+            super(isReferenceBoolCounted, isBlankCounted);
+        }
+
+        @Override
+        protected double evaluate(double[] values) throws EvaluationException {
+            return values[0];
+        }
+    }
 }