Function Names
sum if, sum if any, product if, product if any, product ignore blanks if, product ignore blanks if any, product ignore zero if, product ignore zero if any, average if, average if any, average ignore blanks if, average ignore blanks if any, average ignore zero if, average ignore zero if any, rms if, rms if any, rms ignore blanks if, rms ignore blanks if any, rms ignore zero if, rms ignore zero if any, geometric mean if, geometric mean if any, geometric mean ignore blanks if, geometric mean ignore blanks if any, geometric mean ignore zero if, geometric mean ignore zero if any, harmonic mean if, harmonic mean if any, median if, median if any, median ignore zero if, median ignore zero if any, variance if, variance if any, variance ignore blanks if, variance ignore blanks if any, variance ignore zero if, variance ignore zero if any, deviation if, deviation if any, deviation ignore blanks if, deviation ignore blanks if any, deviation ignore zero if, deviation ignore zero if any, parallel if, parallel if any, count if, count if any, count ignore blanks if, count ignore blanks if any, count ignore zero if, count ignore zero if any, count ignore both if, count ignore both if any, max if, max if any, max ignore zero if, max ignore zero if any, max ignore blanks if, max ignore blanks if any, min if, min if any, min ignore zero if, min ignore zero if any, min ignore blanks if, min ignore blanks if any, max abc if, max abc if any, min abc if, min abc if any, min abc ignore blanks if, min abc ignore blanks if any, max abc ignore case if, min abc ignore case if any, min abc ignore case if, min abc ignore case if any, min abc ignore both if, min abc ignore both if any, max string if, max string if any, min string if, min string if any, min string ignore blanks if, min string ignore blanks if any, max string ignore case if, min string ignore case if any, min string ignore case if, min string ignore case if any, min string ignore both if, min string ignore both if any, max 123 if, max 123 if any, min 123 if, min 123 if any, max 123 ignore zero if, max 123 ignore zero if any, min 123 ignore zero if, min 123 ignore zero if any, max numeral if, max numeral if any, min numeral if, min numeral if any, max numeral ignore zero if, max numeral ignore zero if any, min numeral ignore zero if, min numeral ignore zero if any, join if, join if any, join ignore blanks if, join ignore blanks if any, join unique if, join if any, join unique ignore blanks if, join ignore blanks if any, include if, include if any, exclude if, exclude if anyDescription
The conditional combination functions apply a specific function not on all values supplied, but on a selected set of values for which the comparison specified in the following
function parameters is matching. The function names belonging to this function family consist of previously defined base function names, for example sum, count, average, min 123,
followed by a suffix which is either if or if any. For the base function names, please refer to the corresponding function desriptions in the sections Series Functions and Statistics Functions.
The series of values must be provided in a set and then provided in the 1st function parameter. Simply putting the values into individual fuction parameters like in the original
functions is not allowed here.
The 2nd (and even numbered) function parameters also contain a set containing values where the number of elements should be the same as in the 1st function parameter containing the values to process
because each element in the 2nd (and even numbered) parameter corresponds to their counter part elements in the 1st parameter.
The 3rd (and odd numbered) function parameters contain a comparison expression which can either be a value (implies equal comparison) or an expression containing a comparison operator followed
by an expression as expected in comparisons. All values in the 2nd (and even numbered) function parameters will be compared with this expression. For all comparisons turning out true, the corresponding value
in the 1st parameter will be included in the calculation.
- If the 2nd (and even numbered) function parameters contain more elements than the set in the 1st function parameter, then the excessive elements will be ignored.
- If the 2nd (and even numbered) function parameters contain fewer elements than the set in the 1st function parameter, then false is assumed for the remaining parameters in the 1st function parameter and will not be considered for the calculations.
Example: sum if( {1,2,3,4}, {a,b,a,c}, (=a) ) sums the 1st and 3rd parameter element (values 1, 3) because the corresponding elements in the 2nd set meet the expression equals to a.
The function include if just returns the set containing all values identified by the comparisons done here.
The differenntiation between if or if any at the end of the function names play a role if additional comparisons are specified using additional two function parameters.
- The ending if includes those set elements in the 1st function parameter for calculations if the corresponding elements in all even numbered parameters result in a positive comparison.
In shoret: AND combination of all comparisons. - The ending if any includes those set elements in the 1st function parameter for calculations if the corresponding elements in any of the even numbered parameters result in a positive comparison.
In shoret: OR combination of all comparisons.
Attention: The function nnames beginning with "join " requires 1 additional parameter to provide the separation symbols
Call as: function
Parameter count
3, 5, 7, ... (if not 'join ...'), and 4, 6, 8 ... (if 'join ...')
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
set | Values to calculate These are the values which will be pre-selected and then passed to the base function. |
| 2,4,... input |
set | Corresponding values to compare Recommended to provide the same number of elements as provided in the 1st function parameter. |
| 3,5,... code |
comparison expression :string |
Comparison expression The parameter elements in the previous function parameter will be compoared with this expression. |
| Opt. Last input |
set containing strings | Separation symbols for function names beginning wtih join... This set must contain at least 0 and at most 3 elements of type string. These 3 elements correspond to the 2nd, 3rd and 4th function parameters in the function join(). |
Return value
| Type | Description |
|---|---|
| numeral | Result Calculated value |
Exceptions
Function names containing 'numeral' assert exceptions if values provided are not of type numeral.
Examples
echo( sum if ( { 1, 2, 3, 4, 5 }, { 10, 8, 6, 4, 2 }, >4 ) ); // 6, because first 3 elements are added
echo( sum if ( { 1, 2, 3, 4, 5 }, { 2, 8, 6, 4, 2 }, >=4, { a, b, c, d, e }, c..e ) );
// Returns 7 because comparisons of the 2nd-4th elements in 2nd parameter and last 3 elements in the 4th
// parameter givei true. Intersecting both, the 3rd and 4th elements will be summed.
echo( sum if any ( { 1, 2, 3, 4, 5 }, { 2, 8, 6, 4, 2 }, >=4, { a, b, c, d, e }, c..e ) );
// Returns 14 because comparisons of the 2nd-4th elements in 2nd parameter and last 3 elements in the 4th
// parameter give true. Taking the union of both, the 2nd - 5th elements will be summed.
echo( join if ( { Jim, Jane, John, Kate, Nic }, { 25, 18, 30, 17, 40 }, >=20, {"(", ", ",")"} ) );
// Returns (Jim, John, Nic) because their ages are 20 or higher.Output
6
7
14
(Jim, John, Nic)