Author: fanningpj
Date: Fri Apr 29 19:55:12 2022
New Revision: 1900394
URL: http://svn.apache.org/viewvc?rev=1900394&view=rev
Log:
[github-330] add averageif function support
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java
(with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java
(with props)
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1900394&r1=1900393&r2=1900394&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Fri Apr 29 19:55:12 2022
@@ -68,7 +68,7 @@ public final class AnalysisToolPak imple
r(m, "ACCRINTM", null);
r(m, "AMORDEGRC", null);
r(m, "AMORLINC", null);
- r(m, "AVERAGEIF", null);
+ r(m, "AVERAGEIF", AverageIf.instance );
r(m, "AVERAGEIFS", Averageifs.instance);
r(m, "BAHTTEXT", null);
r(m, "BESSELI", null);
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java?rev=1900394&view=auto
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java
(added)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java
Fri Apr 29 19:55:12 2022
@@ -0,0 +1,118 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.AreaEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
+
+/**
+ * Handler for singular AverageIf which has different operand handling than
+ * the generic AverageIfs version.
+ */
+public class AverageIf extends Baseifs {
+
+ public static final FreeRefFunction instance = new Averageifs();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] _args, OperationEvaluationContext
_ec) {
+ if (_args.length < 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ try {
+ AreaEval sumRange = convertRangeArg(_args[0]);
+
+ if (_args.length == 3) {
+ sumRange = convertRangeArg(_args[2]);
+ }
+
+ // collect pairs of ranges and criteria
+ AreaEval ae = convertRangeArg(_args[0]);
+ I_MatchPredicate mp = Countif.createCriteriaPredicate(_args[1],
_ec.getRowIndex(), _ec.getColumnIndex());
+
+ if (mp instanceof Countif.ErrorMatcher) {
+ throw new
EvaluationException(ErrorEval.valueOf(((Countif.ErrorMatcher) mp).getValue()));
+ }
+
+ return aggregateMatchingCells(createAggregator(), sumRange, ae,
mp);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+
+ protected ValueEval aggregateMatchingCells(Aggregator aggregator, AreaEval
sumRange, AreaEval testRange, I_MatchPredicate mp)
+ throws EvaluationException {
+
+ int height = testRange.getHeight();
+ int width = testRange.getWidth();
+
+ for (int r = 0; r < height; r++) {
+ for (int c = 0; c < width; c++) {
+
+ ValueEval _testValue = testRange.getRelativeValue(r, c);
+ ;
+
+ ValueEval _sumValue = sumRange.getRelativeValue(r, c);
+
+ if (mp != null && mp.matches(_testValue)) { // aggregate only
if all of the corresponding criteria specified are true for that cell.
+ if (_testValue instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) _testValue);
+ }
+ aggregator.addValue(_sumValue);
+ }
+ }
+ }
+ return aggregator.getResult();
+ }
+
+ @Override
+ protected boolean hasInitialRange() {
+ return false;
+ }
+
+
+ @Override
+ protected Aggregator createAggregator() {
+
+ return new Aggregator() {
+ Double sum = 0.0;
+ Integer count = 0;
+
+ @Override
+ public void addValue(ValueEval value) {
+
+ if (!(value instanceof NumberEval)) return;
+
+ double d = ((NumberEval) value).getNumberValue();
+ sum += d;
+ count++;
+
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return count == 0 ? ErrorEval.DIV_ZERO : new NumberEval(sum /
count);
+ }
+ };
+ }
+
+}
Propchange:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AverageIf.java
------------------------------------------------------------------------------
svn:eol-style = native
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java?rev=1900394&view=auto
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java
(added)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java
Fri Apr 29 19:55:12 2022
@@ -0,0 +1,120 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import static org.apache.poi.ss.util.Utils.*;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.NumericValueEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+
+/**
+ * Test cases for AVERAGEIFS()
+ */
+final class TestAverageIf {
+
+ private static final OperationEvaluationContext EC = new
OperationEvaluationContext(null, null, 0, 0, 0, null);
+
+ private static ValueEval invokeAverageif(ValueEval[] args) {
+ return new AverageIf().evaluate(args, EC);
+ }
+
+ private static void confirmDouble(double expected, ValueEval actualEval) {
+ assertTrue(actualEval instanceof NumericValueEval, "Expected numeric
result");
+ NumericValueEval nve = (NumericValueEval) actualEval;
+ assertEquals(expected, nve.getNumberValue(), 0);
+ }
+
+ private static void confirm(double expectedResult, ValueEval[] args) {
+ confirmDouble(expectedResult, invokeAverageif(args));
+ }
+
+ private static void confirmError(ErrorEval errorEval, ValueEval[] args) {
+ ValueEval actualEval = invokeAverageif(args);
+ assertEquals(errorEval, actualEval);
+ }
+
+ /**
+ * Example 1 from
+ *
https://support.microsoft.com/en-us/office/averageif-function-faec8e2e-0dec-4308-af69-f5576d8ac642
+ */
+ @Test
+ void testExample1() {
+ ValueEval[] b2b5 = new ValueEval[]{
+ new NumberEval(7000),
+ new NumberEval(14000),
+ new NumberEval(21000),
+ new NumberEval(28000)
+ };
+
+ ValueEval[] args;
+ // "=AVERAGEIF(B2:B5, "<23000")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("B2:B5", b2b5),
+ new StringEval("<23000")
+ };
+ confirm(14000, args);
+
+ ValueEval[] a2a5 = new ValueEval[]{
+ new NumberEval(100000),
+ new NumberEval(200000),
+ new NumberEval(300000),
+ new NumberEval(400000)
+ };
+ // "=AVERAGEIF(A2:A5, "<250000", A2:A5)"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A5", a2a5),
+ new StringEval("<250000"),
+ EvalFactory.createAreaEval("A2:A5", a2a5)
+ };
+ confirm(150000, args);
+
+ // "=AVERAGEIF(A2:A5, "<95000")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A5", a2a5),
+ new StringEval("<95000"),
+ EvalFactory.createAreaEval("A2:A5", a2a5)
+ };
+
+ confirmError(ErrorEval.DIV_ZERO, args);
+
+ // "=AVERAGEIF(A2:A5, "<95000", B2:B5 )"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A5", a2a5),
+ new StringEval(">250000"),
+ EvalFactory.createAreaEval("B2:B5", b2b5)
+ };
+ confirm(24500, args);
+ }
+
+}
Propchange:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageIf.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
