From e399507710813886ec1f50291d378e282dfe6ce8 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sun, 16 Dec 2018 18:51:09 +0000 Subject: [PATCH] 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 --- .../poi/ss/formula/eval/FunctionEval.java | 2 +- .../formula/functions/AggregateFunction.java | 311 +++++++++--------- .../MultiOperandNumericFunction.java | 18 +- .../poi/ss/formula/functions/TestGeomean.java | 112 +++++++ .../TestMultiOperandNumericFunction.java | 23 ++ 5 files changed, 308 insertions(+), 158 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/formula/functions/TestGeomean.java diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java index 961a9cd81c..1f405ed45e 100644 --- a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -299,7 +299,7 @@ public final class FunctionEval { // 316: TTEST // 317: PROB retval[318] = AggregateFunction.DEVSQ; - // 319: GEOMEAN + retval[319] = AggregateFunction.GEOMEAN; // 320: HARMEAN retval[321] = AggregateFunction.SUMSQ; // 322: KURT diff --git a/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java b/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java index 644086d31e..9011de1469 100644 --- a/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java +++ b/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java @@ -17,6 +17,7 @@ package org.apache.poi.ss.formula.functions; +import org.apache.commons.math3.stat.descriptive.moment.GeometricMean; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; import org.apache.poi.ss.formula.eval.NumberEval; @@ -28,45 +29,45 @@ import org.apache.poi.ss.formula.eval.ValueEval; */ public abstract class AggregateFunction extends MultiOperandNumericFunction { - private static final class LargeSmall extends Fixed2ArgFunction { - private final boolean _isLarge; - protected LargeSmall(boolean isLarge) { - _isLarge = isLarge; - } + private static final class LargeSmall extends Fixed2ArgFunction { + private final boolean _isLarge; + protected LargeSmall(boolean isLarge) { + _isLarge = isLarge; + } - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, - ValueEval arg1) { - double dn; - try { - ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); - dn = OperandResolver.coerceValueToDouble(ve1); - } catch (EvaluationException e1) { - // all errors in the second arg translate to #VALUE! - return ErrorEval.VALUE_INVALID; - } - // weird Excel behaviour on second arg - if (dn < 1.0) { - // values between 0.0 and 1.0 result in #NUM! - return ErrorEval.NUM_ERROR; - } - // all other values are rounded up to the next integer - int k = (int) Math.ceil(dn); + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + double dn; + try { + ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); + dn = OperandResolver.coerceValueToDouble(ve1); + } catch (EvaluationException e1) { + // all errors in the second arg translate to #VALUE! + return ErrorEval.VALUE_INVALID; + } + // weird Excel behaviour on second arg + if (dn < 1.0) { + // values between 0.0 and 1.0 result in #NUM! + return ErrorEval.NUM_ERROR; + } + // all other values are rounded up to the next integer + int k = (int) Math.ceil(dn); - double result; - try { - double[] ds = ValueCollector.collectValues(arg0); - if (k > ds.length) { - return ErrorEval.NUM_ERROR; - } - result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k); - NumericFunction.checkValue(result); - } catch (EvaluationException e) { - return e.getErrorEval(); - } + double result; + try { + double[] ds = ValueCollector.collectValues(arg0); + if (k > ds.length) { + return ErrorEval.NUM_ERROR; + } + result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k); + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } - return new NumberEval(result); - } - } + return new NumberEval(result); + } + } /** * Returns the k-th percentile of values in a range. You can use this function to establish a threshold of @@ -84,67 +85,67 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction { *
  • If k is not a multiple of 1/(n - 1), PERCENTILE interpolates to determine the value at the k-th percentile.
    • * */ - private static final class Percentile extends Fixed2ArgFunction { - - protected Percentile() { - } + private static final class Percentile extends Fixed2ArgFunction { - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, - ValueEval arg1) { - double dn; - try { - ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); - dn = OperandResolver.coerceValueToDouble(ve1); - } catch (EvaluationException e1) { - // all errors in the second arg translate to #VALUE! - return ErrorEval.VALUE_INVALID; - } - if (dn < 0 || dn > 1) { // has to be percentage - return ErrorEval.NUM_ERROR; - } + protected Percentile() { + } - double result; - try { - double[] ds = ValueCollector.collectValues(arg0); - int N = ds.length; + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, + ValueEval arg1) { + double dn; + try { + ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex); + dn = OperandResolver.coerceValueToDouble(ve1); + } catch (EvaluationException e1) { + // all errors in the second arg translate to #VALUE! + return ErrorEval.VALUE_INVALID; + } + if (dn < 0 || dn > 1) { // has to be percentage + return ErrorEval.NUM_ERROR; + } - if (N == 0 || N > 8191) { + double result; + try { + double[] ds = ValueCollector.collectValues(arg0); + int N = ds.length; + + if (N == 0 || N > 8191) { return ErrorEval.NUM_ERROR; } - double n = (N - 1) * dn + 1; - if (n == 1d) { - result = StatsLib.kthSmallest(ds, 1); - } else if (Double.compare(n, N) == 0) { - result = StatsLib.kthLargest(ds, 1); - } else { - int k = (int) n; - double d = n - k; - result = StatsLib.kthSmallest(ds, k) + d - * (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k)); - } + double n = (N - 1) * dn + 1; + if (n == 1d) { + result = StatsLib.kthSmallest(ds, 1); + } else if (Double.compare(n, N) == 0) { + result = StatsLib.kthLargest(ds, 1); + } else { + int k = (int) n; + double d = n - k; + result = StatsLib.kthSmallest(ds, k) + d + * (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k)); + } + + NumericFunction.checkValue(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } - NumericFunction.checkValue(result); - } catch (EvaluationException e) { - return e.getErrorEval(); - } + return new NumberEval(result); + } + } - return new NumberEval(result); - } - } - - static final class ValueCollector extends MultiOperandNumericFunction { - private static final ValueCollector instance = new ValueCollector(); - public ValueCollector() { - super(false, false); - } - public static double[] collectValues(ValueEval...operands) throws EvaluationException { - return instance.getNumberArray(operands); - } - protected double evaluate(double[] values) { - throw new IllegalStateException("should not be called"); - } - } + static final class ValueCollector extends MultiOperandNumericFunction { + private static final ValueCollector instance = new ValueCollector(); + public ValueCollector() { + super(false, false); + } + public static double[] collectValues(ValueEval...operands) throws EvaluationException { + return instance.getNumberArray(operands); + } + protected double evaluate(double[] values) { + throw new IllegalStateException("should not be called"); + } + } protected AggregateFunction() { super(false, false); @@ -181,66 +182,66 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction { } public static final Function AVEDEV = new AggregateFunction() { - protected double evaluate(double[] values) { - return StatsLib.avedev(values); - } - }; - public static final Function AVERAGE = new AggregateFunction() { - protected double evaluate(double[] values) throws EvaluationException { - if (values.length < 1) { - throw new EvaluationException(ErrorEval.DIV_ZERO); - } - return MathX.average(values); - } - }; - public static final Function DEVSQ = new AggregateFunction() { - protected double evaluate(double[] values) { - return StatsLib.devsq(values); - } - }; - public static final Function LARGE = new LargeSmall(true); - public static final Function MAX = new AggregateFunction() { - protected double evaluate(double[] values) { - return values.length > 0 ? MathX.max(values) : 0; - } - }; - public static final Function MEDIAN = new AggregateFunction() { - protected double evaluate(double[] values) { - return StatsLib.median(values); - } - }; - public static final Function MIN = new AggregateFunction() { - protected double evaluate(double[] values) { - return values.length > 0 ? MathX.min(values) : 0; - } - }; - - public static final Function PERCENTILE = new Percentile(); - - public static final Function PRODUCT = new AggregateFunction() { - protected double evaluate(double[] values) { - return MathX.product(values); - } - }; - public static final Function SMALL = new LargeSmall(false); - public static final Function STDEV = new AggregateFunction() { - protected double evaluate(double[] values) throws EvaluationException { - if (values.length < 1) { - throw new EvaluationException(ErrorEval.DIV_ZERO); - } - return StatsLib.stdev(values); - } - }; - public static final Function SUM = new AggregateFunction() { - protected double evaluate(double[] values) { - return MathX.sum(values); - } - }; - public static final Function SUMSQ = new AggregateFunction() { - protected double evaluate(double[] values) { - return MathX.sumsq(values); - } - }; + protected double evaluate(double[] values) { + return StatsLib.avedev(values); + } + }; + public static final Function AVERAGE = new AggregateFunction() { + protected double evaluate(double[] values) throws EvaluationException { + if (values.length < 1) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return MathX.average(values); + } + }; + public static final Function DEVSQ = new AggregateFunction() { + protected double evaluate(double[] values) { + return StatsLib.devsq(values); + } + }; + public static final Function LARGE = new LargeSmall(true); + public static final Function MAX = new AggregateFunction() { + protected double evaluate(double[] values) { + return values.length > 0 ? MathX.max(values) : 0; + } + }; + public static final Function MEDIAN = new AggregateFunction() { + protected double evaluate(double[] values) { + return StatsLib.median(values); + } + }; + public static final Function MIN = new AggregateFunction() { + protected double evaluate(double[] values) { + return values.length > 0 ? MathX.min(values) : 0; + } + }; + + public static final Function PERCENTILE = new Percentile(); + + public static final Function PRODUCT = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.product(values); + } + }; + public static final Function SMALL = new LargeSmall(false); + public static final Function STDEV = new AggregateFunction() { + protected double evaluate(double[] values) throws EvaluationException { + if (values.length < 1) { + throw new EvaluationException(ErrorEval.DIV_ZERO); + } + return StatsLib.stdev(values); + } + }; + public static final Function SUM = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.sum(values); + } + }; + public static final Function SUMSQ = new AggregateFunction() { + protected double evaluate(double[] values) { + return MathX.sumsq(values); + } + }; public static final Function VAR = new AggregateFunction() { protected double evaluate(double[] values) throws EvaluationException { if (values.length < 1) { @@ -257,4 +258,16 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction { return StatsLib.varp(values); } }; + public static final Function GEOMEAN = new AggregateFunction() { + @Override + protected double evaluate(double[] values) throws EvaluationException { + // The library implementation returns 0 for an input sequence like [1, 0]. So this check is necessary. + for (double value: values) { + if (value <= 0) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } + return new GeometricMean().evaluate(values, 0, values.length); + } + }; } diff --git a/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java b/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java index 7f07d7e793..dae92cb6af 100644 --- a/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java +++ b/src/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java @@ -24,6 +24,7 @@ import org.apache.poi.ss.formula.eval.BlankEval; import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.ErrorEval; 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.NumericValueEval; import org.apache.poi.ss.formula.eval.OperandResolver; @@ -85,19 +86,16 @@ public abstract class MultiOperandNumericFunction implements Function { private static final int DEFAULT_MAX_NUM_OPERANDS = SpreadsheetVersion.EXCEL2007.getMaxFunctionArgs(); public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { - - double d; try { double[] values = getNumberArray(args); - d = evaluate(values); + double d = evaluate(values); + if (Double.isNaN(d) || Double.isInfinite(d)) { + return ErrorEval.NUM_ERROR; + } + return new NumberEval(d); } catch (EvaluationException e) { return e.getErrorEval(); } - - if (Double.isNaN(d) || Double.isInfinite(d)) - return ErrorEval.NUM_ERROR; - - return new NumberEval(d); } protected abstract double evaluate(double[] values) throws EvaluationException; @@ -217,6 +215,10 @@ public abstract class MultiOperandNumericFunction implements Function { } return; } + if (ve == MissingArgEval.instance) { + temp.add(0.0); + return; + } throw new RuntimeException("Invalid ValueEval type passed for conversion: (" + ve.getClass() + ")"); } 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]; + } + } } -- 2.39.5