Apache POI - Common 5.3.0 · org.apache.poi.ss.formula.functions.Sumproduct

Implementation for the Excel function SUMPRODUCT

Syntax :
SUMPRODUCT ( array1[, array2[, array3[, ...]]])

Parameter descriptions
array1, ... arrayN	typically area references, possibly cell references or scalar values

Let An_(i,j) represent the element in the ith row jth column of the nth array
Assuming each array has the same dimensions (W, H), the result is defined as:
SUMPRODUCT = Σ_{i: 1..H} ( Σ_{j: 1..W} ( Π_{n: 1..N} An_(i,j) ) )

The current implementation does not support the more advanced use cases of SUMPRODUCT, including SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9) (see example 3 in https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e).

Constructor Summary

Access	Constructor and Description
public	Sumproduct()

Method Summary

Modifier and Type	Method and Description
private static boolean	areasAllSameSize(TwoDEval[] args, int height, int width)
public ValueEval	evaluate(ValueEval[] the evaluated function arguments. Empty values are represented with `BlankEval` or `MissingArgEval`, never `null`. args, int row index of the cell containing the formula under evaluation srcCellRow, int column index of the cell containing the formula under evaluation srcCellCol) Implements org.apache.poi.ss.formula.functions.Function.evaluate.
private static ValueEval	evaluateAreaSumProduct(ValueEval[] evalArgs)
private static ValueEval	evaluateSingleProduct(ValueEval[] evalArgs)
private static double	getProductTerm(ValueEval ve, boolean `false` for SUMPRODUCTs over area refs. isScalarProduct) Determines a `double` value for the specified `ValueEval`.
private static double	getScalarValue(ValueEval arg)
private static void	throwFirstError(TwoDEval areaEval)

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

Constructor Detail

Sumproduct	back to summary
public Sumproduct()

Method Detail

areasAllSameSize	back to summary
private static boolean areasAllSameSize(TwoDEval[] args, int height, int width)

evaluate back to summary

evaluate	back to summary
public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) Implements org.apache.poi.ss.formula.functions.Function.evaluate. Parameters args:ValueEval[] Doc from org.apache.poi.ss.formula.functions.Function.evaluate. the evaluated function arguments. Empty values are represented with `BlankEval` or `MissingArgEval`, never `null`. srcCellRow:int Doc from org.apache.poi.ss.formula.functions.Function.evaluate. row index of the cell containing the formula under evaluation srcCellCol:int Doc from org.apache.poi.ss.formula.functions.Function.evaluate. column index of the cell containing the formula under evaluation Returns:ValueEval Doc from org.apache.poi.ss.formula.functions.Function.evaluate. The evaluated result, possibly an `ErrorEval`, never `null` Note - Excel uses the error code #NUM! instead of IEEE NaN, so when numeric functions evaluate to `Double#NaN` be sure to translate the result to `ErrorEval#NUM_ERROR`. Annotations @Override

public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol)

Implements org.apache.poi.ss.formula.functions.Function.evaluate.

Parameters

args:ValueEval[]

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

the evaluated function arguments. Empty values are represented with BlankEval or MissingArgEval, never null.

srcCellRow:int

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

row index of the cell containing the formula under evaluation

srcCellCol:int

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

column index of the cell containing the formula under evaluation

Returns:ValueEval

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

The evaluated result, possibly an ErrorEval, never null

Note

- Excel uses the error code #NUM! instead of IEEE NaN, so when numeric functions evaluate to Double#NaN be sure to translate the result to ErrorEval#NUM_ERROR.

Annotations

@Override

evaluateAreaSumProduct	back to summary
private static ValueEval evaluateAreaSumProduct(ValueEval[] evalArgs) throws EvaluationException

evaluateSingleProduct	back to summary
private static ValueEval evaluateSingleProduct(ValueEval[] evalArgs) throws EvaluationException

getProductTerm back to summary

getProductTerm	back to summary
private static double getProductTerm(ValueEval ve, boolean isScalarProduct) throws EvaluationException Determines a `double` value for the specified `ValueEval`. Parameters isScalarProduct:boolean `false` for SUMPRODUCTs over area refs. Exceptions EvaluationException: if `ve` represents an error value. Note - string values and empty cells are interpreted differently depending on `isScalarProduct`. For scalar products, if any term is blank or a string, the error (#VALUE!) is raised. For area (sum)products, if any term is blank or a string, the result is zero.

private static double getProductTerm(ValueEval ve, boolean isScalarProduct) throws EvaluationException

Determines a double value for the specified ValueEval.

Parameters

isScalarProduct:boolean: false for SUMPRODUCTs over area refs.

Exceptions

EvaluationException:: if ve represents an error value.
Note - string values and empty cells are interpreted differently depending on isScalarProduct. For scalar products, if any term is blank or a string, the error (#VALUE!) is raised. For area (sum)products, if any term is blank or a string, the result is zero.

getScalarValue	back to summary
private static double getScalarValue(ValueEval arg) throws EvaluationException

throwFirstError	back to summary
private static void throwFirstError(TwoDEval areaEval) throws EvaluationException

public final Class Sumproduct

Constructor Summary

Method Summary

Constructor Detail

Method Detail