REDUCE function

REDUCE(values, itemProcessor, initialAccumulator?, resultProcessor?) REDUCE(values; itemProcessor; initialAccumulator?; resultProcessor?)

values — { ? }

The array of values to reduce.

itemProcessor

A formula fragment which is invoked once for every given value, and is expected to return the new accumulator. To do its work, it has access to four parameters: Accumulator holds the current value of the accumulator, Value holds the current element of the array being processed and Index holds the current one-based position of the element of the array being processed. (If Index is 1, that means that the first value provided to this function is being processed.) Finally, Source is a reference to the array given as the first parameter to this function.

initialAccumulator (optional)

The initial value of the accumulator.

resultProcessor (optional)

A formula fragment which returns the value returned by the function. To do its work, it has access to three parameters: Accumulator, the last value returned from the itemProcessor formula fragment. Size holds the number of processed values and Source is a reference to the array given as the first parameter to this function. (SIZE(Source) may differ from the Size parameter if the given array of values contains references to other arrays.)

Returns

The value returned by the result processor.

Reduces an array of values to a single value using a user-supplied formula. This is arguably Calcapp's most powerful function and can be used to replicate the functionality of many of Calcapp's built-in functions, including SUM, AND, OR, XOR, MIN, MAX and AVERAGE.

REDUCE is Calcapp-specific (but is widely used in traditional programming).

Implementing SUM with REDUCE

Here's how the SUM function—which adds all numbers given to it together—would be implemented using REDUCE:

REDUCE({ 1, 2, 3 }, Accumulator + Value, 0)REDUCE({ 1; 2; 3 }; Accumulator + Value; 0)

This formula returns 1 + 2 + 3 = 61 + 2 + 3 = 6.

REDUCE invokes the formula fragment given as the second parameter (Accumulator + ValueAccumulator + Value above) once for every element of the given array. This second parameter can refer to two named values anywhere in its body: Accumulator holds the value returned by the prior invocation of the second parameter formula fragment and Value holds the current array element being processed. The first time the second parameter formula fragment is run, Accumulator is set to the value of the third parameter, 0, specifying the initial accumulator.

(There are two other named values, which are discussed in the parameter documentation, under Details above.)

The first time the second parameter, Accumulator + ValueAccumulator + Value, is invoked, Accumulator is set to 0 (again, the value of the third parameter) and Value is set to 1 (the first element of the array given as the first parameter), meaning that 1 is returned by the second parameter formula fragment. The second time it is invoked, Accumulator is set to 1 and Value is set to 2, meaning that 3 is returned. The final and third time the second parameter is invoked, Accumulator is set to 3 and Value is set to 3, meaning that 6 is returned. This is the value ultimately returned from the invocation of REDUCE.

You can name values passed to a function parameter anything you like. While Accumulator is a good name in the general sense, in this case it represents the sum. To name it that, we can use ->:

REDUCE({ 1, 2, 3 }, Sum -> Sum + Value, 0)REDUCE({ 1; 2; 3 }; Sum -> Sum + Value; 0)

To name both named values with one-letter names, this invocation can be used:

REDUCE({ 1, 2, 3 }, (S, V) -> S + V, 0)REDUCE({ 1; 2; 3 }; (S; V) -> S + V; 0)

In a case like this, the third parameter (specifying the initial value for the accumulator) can actually be omitted. This formula also calculates the sum:

REDUCE({ 1, 2, 3 }, Accumulator + Value)REDUCE({ 1; 2; 3 }; Accumulator + Value)

When the third parameter is omitted, the second parameter formula fragment is not invoked for the first element. Instead, the accumulator is initially set to the first element of the array, which works well for implementing a sum. As a result, the first time the second parameter formula fragment is run, the accumulator is set to 1 and the value is set to 2, meaning that 3 is returned. The second and final time the second parameter formula fragment is run, the accumulator is set to 3 and the value is also set to 3, meaning that 6 is returned.

Adding numbers together using REDUCE works with or without a third parameter, but some operations, like finding the largest value in an array, are more elegantly expressed without a third parameter. See below for more information.

Implementing AVERAGE with REDUCE

Here's how the AVERAGE function can be implemented using REDUCE:

REDUCE({ 1, 2, 3 }, Accumulator + Value, 0, Accumulator / Size)REDUCE({ 1; 2; 3 }; Accumulator + Value; 0; Accumulator / Size)

Apart from the new fourth parameter, AVERAGE is identical to SUM. The fourth parameter may be set to a formula fragment which calculates a final value.

The fourth parameter formula fragment has access to two named values, Accumulator, the last value returned from the second parameter, and Size, the number of array elements that have been processed. Calculating the average can be done by dividing the accumulator by the size: Accumulator / SizeAccumulator / Size.

You would normally not provide explicit values as the first parameter, but instead reference some other array (like NumberField1:NumberField10NumberField1:NumberField10, which is an array containing the values of NumberField1 and NumberField10 and all number fields appearing between them). Those arrays can contain blank values, which shouldn't be considered by AVERAGE. To filter them out, use FILTER:

REDUCE(FILTER(NumberField1:NumberField10, ISDEFINED(Element)), Accumulator + Value, 0, Accumulator / Size)REDUCE(FILTER(NumberField1:NumberField10; ISDEFINED(Element)); Accumulator + Value; 0; Accumulator / Size)

As discussed in the section on implementing SUM with REDUCE, the initial value, 0, can be left out. That also works when calculating the average value, meaning that this formula works just as well as the one above:

REDUCE(FILTER(NumberField1:NumberField10, ISDEFINED(Element)), Accumulator + Value, Accumulator / Size)REDUCE(FILTER(NumberField1:NumberField10; ISDEFINED(Element)); Accumulator + Value; Accumulator / Size)

Implementing MAX and MIN with REDUCE

MIN returns the smallest value in an array and MAX does the opposite and returns the largest value in an array. Here's an implementation of MAX using REDUCE:

REDUCE({ 1, 2, -10 }, IF(Value > Accumulator, Value, Accumulator))REDUCE({ 1; 2; -10 }; IF(Value > Accumulator; Value; Accumulator))

As no third parameter is specified, the initial value for the accumulator is set to 1. The second parameter formula fragment then uses IF to determine if the array element being processed is larger than the accumulator. If so, that value is returned. Otherwise, the existing accumulator is returned unchanged.

The first time the second parameter formula fragment is run, the accumulator is set to 1 and the value is set to 2. As 2 is larger than 1, that becomes the new value of the accumulator. The second and final time the second parameter formula fragment is run, though, the accumulator is set to 2 and the value is set to -10. As -10 is not larger than 2, 2 is returned. That means that the value returned from REDUCE is 2, the largest value of the array.

This is an example of an operation where it is not appropriate to specify a third parameter to REDUCE (which sets the initial accumulator). Here's an attempt at realizing MAX with the third parameter:

REDUCE({ 1, 2, -10 }, IF(Value > Accumulator, Value, Accumulator), -10000)REDUCE({ 1; 2; -10 }; IF(Value > Accumulator; Value; Accumulator); -10000)

However, the formula above only works for array elements which are less than -10000.

MIN is implemented simply by replacing the > operator with the < operator:

REDUCE({ 1, 2, -10 }, IF(Value < Accumulator, Value, Accumulator))REDUCE({ 1; 2; -10 }; IF(Value < Accumulator; Value; Accumulator))

Implementing AND with REDUCE

AND returns TRUE if all array elements given to it are TRUE. Otherwise, it returns FALSE. Here's how it can be implemented using REDUCE:

REDUCE({ TRUE, FALSE, TRUE }, IF(Accumulator, Value, FALSE), TRUE)REDUCE({ TRUE; FALSE; TRUE }; IF(Accumulator; Value; FALSE); TRUE)

The initial value for the accumulator is set to TRUE (the third parameter), meaning that the first time the second parameter IF(Accumulator, Value, FALSE)IF(Accumulator; Value; FALSE) is invoked, Accumulator is set to TRUE and Value is also set to TRUE, meaning that TRUE is the result. The second time the parameter is invoked, Accumulator is set to TRUE (the prior return value from the second parameter) and Value is set to FALSE, meaning that FALSE is the result. The final time the second parameter is invoked, Accumulator is set to FALSE and Value is set to TRUE, meaning that FALSE is ultimately returned from the invocation of REDUCE, which is the correct result.

Implementing XOR with REDUCE

XOR, also known as "exclusive or," is often thought of as being like OR, only that it returns FALSE when both parameters are TRUE. Like OR and AND, though, XOR can be invoked with an arbitrary number of parameters. In this more general sense, XOR returns TRUE only when an odd number of parameters are TRUE, and FALSE otherwise.

XOR requires the use of the fourth parameter to REDUCE:

REDUCE({ TRUE, FALSE, TRUE }, IF(Value, Accumulator + 1, Accumulator), 0, ISODD(Accumulator))REDUCE({ TRUE; FALSE; TRUE }; IF(Value; Accumulator + 1; Accumulator); 0; ISODD(Accumulator))

The initial accumulator is set to 0, and the second parameter formula fragment IF(Value, Accumulator + 1, Accumulator)IF(Value; Accumulator + 1; Accumulator) adds one to the accumulator only if the array element being processed is TRUE. What happens, essentially, is that the second parameter formula fragment counts the number of TRUE values in the array given as the first parameter.

The fourth parameter completes the implementation by returning whether the accumulator is an odd number. (The value returned by the fourth parameter, if given, is used as the return value of REDUCE. It is only invoked once, and can be thought of as an opportunity to return a final value.)

It would actually be simpler to implement XOR without using REDUCE. The -- operator can be used to convert logical arrays to number arrays, turning TRUE values into 1 values and FALSE values into 0 values. SUM can then be used to count the number of TRUE values, which is then passed to the ISODD function:

ISODD(SUM(--{TRUE, FALSE, TRUE}))ISODD(SUM(--{TRUE; FALSE; TRUE}))

Counting the longest word in a sentence with REDUCE

Here's how REDUCE can be used to count the number of characters in the longest word in a sentence with REDUCE:

REDUCE(TEXTSPLIT("This is a test", " "), MAX(LEN(Value), Accumulator), 0)REDUCE(TEXTSPLIT("This is a test"; " "); MAX(LEN(Value); Accumulator); 0)

REDUCE needs its first parameter to be an array, meaning that our first order of business is to break the sentence apart into its constituent units, its characters. This is done using the TEXTSPLIT function, which takes the text string to break apart as its first parameter and the separator as its second parameter, which in this case is a space: TEXTSPLIT("This is a test", " ")TEXTSPLIT("This is a test"; " ").

This part of the formula returns { "This", "is", "a", "test" }{ "This"; "is"; "a"; "test" }. As a result, this array is given as the first parameter to REDUCE.

The initial accumulator is set to zero, indicating that the longest word found initially is zero characters in length. When the second parameter, MAX(LEN(Value), Accumulator)MAX(LEN(Value); Accumulator), is run for the first time, Accumulator is set to 0 and Value is set to "This""This". LEN returns the length of a string, so when applied to "This""This", it returns 4.

MAX returns the largest number it is invoked with. Here, MAX is invoked with 4 and 0 as its parameters (MAX(4, 0)MAX(4; 0)), and returns 4, which is also the result returned by the second parameter the first time it is invoked.

Invoking the second parameter with the values "is", "a" and "test" does not change the accumulator, as none of these words are longer than "This". As a result, REDUCE returns 4, which is the correct answer.

Implementing MATCH with REDUCE

MATCH returns the position of a sought value in a given array. MATCH(30, { 10, 20, 30, 30 }, 0)MATCH(30; { 10; 20; 30; 30 }; 0) returns 3, because 30 is found at position 3 in the array and MATCH scans from left to right and returns the first value that matches. MATCH returns an #N/A error (not available) if a value cannot be found.

This is easy to replicate with REDUCE:

REDUCE( { 10, 20, 30, 30 }, (A, V, I) -> IF(ISNA(A) && (V = 30), I, A), NA())REDUCE( { 10; 20; 30; 30 }; (A; V; I) -> IF(ISNA(A) && (V = 30); I; A); NA())

The initial accumulator is set to an #N/A error, indicating that a value cannot be found, using the NA function. The second parameter formula fragment, (A, V, I) -> IF(ISNA(A) && (V = 30), I, A)(A; V; I) -> IF(ISNA(A) && (V = 30); I; A), returns the position only if the accumulator is an #N/A error and the value matches the value that was sought (30). Otherwise, it returns the accumulator unchanged.

Note that the Accumulator has been renamed A, Value has been renamed V and Index has been renamed I. This is done for brevity.

The accumulator is set to #N/A initially. When the second parameter formula fragment is asked to process the first array element, 10, the condition given to the first parameter of IF, ISNA(A) && (V = 30)ISNA(A) && (V = 30), returns FALSE. While ISNA(A)ISNA(A) returns TRUE, V is equal to 10, not 30, meaning that the result is that the accumulator (equal to #N/A) is returned unchanged.

When 20 is processed, the same process repeats itself. When 30 is processed, however, the IF condition ISNA(A) && (V = 30)ISNA(A) && (V = 30) is equal to TRUE, meaning that the accumulator is set to the given position, which is 3. That's the value we're looking for.

When the second 30 value is processed, the IF condition ISNA(A) && (V = 30)ISNA(A) && (V = 30) is equal to FALSE. While V is indeed equal to 30, the accumulator is no longer equal to the #N/A error, meaning that the existing accumulator (3) is returned unchanged.

The end result is that this REDUCE formula scans an array from left to right, returning the first position of the sought value, or #N/A otherwise.

REDUCE, FILTER and MAP

REDUCE, FILTER and MAP are commonly used together, as a data processing pipeline to transform data. FILTER is used to remove irrelevant elements from an array, MAP is used to transform the remaining elements and REDUCE, finally, is used to transform the resulting array to a single value.

Instead of REDUCE, a simpler function can be used which reduces an array of values to a single value. The most popular such functions are SUM (which adds all array elements together and returns the result) and AVERAGE (which returns an average of all the array elements).

A single formula can use multiple invocations of MAP and FILTER. For instance, an innermost FILTER invocation can filter the raw array once, and then hand this data to MAP, which transforms the filtered data. This data can then, once more, be given to FILTER, which filters out additional elements, and so on.

Consider this formula:

SUM(FILTER(MAP(FILTER({ "$326.60", "€402.80", "$290.00", "$128", "3002 SEK" }, STARTSWITH(Element, "$")), PARSENUMBER(Element)), Element > 200))SUM(FILTER(MAP(FILTER({ "$326.60"; "€402.80"; "$290.00"; "$128"; "3002 SEK" }; STARTSWITH(Element; "$")); PARSENUMBER(Element)); Element > 200))

The formula above filters the text array { "$326.60", "€402.80", "$290.00", "$128", "3002 SEK" }{ "$326.60"; "€402.80"; "$290.00"; "$128"; "3002 SEK" }, which lists amounts in various currencies, leaving only dollar amounts. It then converts these amounts to numbers, keeps only those amounts exceeding $200 and finally returns the sum of these amounts.

Working our way outwards from the text array, FILTER is applied to it and uses the formula fragment STARTSWITH(Element, "$")STARTSWITH(Element; "$") to only include array elements which start with a dollar sign. That leaves { "$326.60", "$290.00", "$128" }{ "$326.60"; "$290.00"; "$128" }.

MAP is applied to this array, with the formula fragment PARSENUMBER(Element)PARSENUMBER(Element), which converts the text array with textual amounts to a number array holding the same amounts: { 326.6, 290, 128 }{ 326,6; 290; 128 }.

FILTER is then applied to this array using this formula fragment, Element > 200Element > 200, which filters out all elements which are not greater than 200. That leaves the array { 326.6, 290 }{ 326,6; 290 }. Finally, SUM is applied to this array, returning the grand total 616.60.

Examples

REDUCE({ 1, 2, 3 }, Accumulator + Value, 0)REDUCE({ 1; 2; 3 }; Accumulator + Value; 0)

Adds all the numbers together, yielding 6. This REDUCE formula does the same thing as the SUM function, except for the fact that it does not filter out blank values. Refer to the main text for details.

REDUCE({ 1, 2, 3 }, (A, V) -> A + V, 0, (A, S) -> A / S)REDUCE({ 1; 2; 3 }; (A; V) -> A + V; 0; (A; S) -> A / S)

Adds all the numbers together, before dividing them by the number of considered values (3). This REDUCE formula does the same thing as the AVERAGE function, except for the fact that it does not filter out blank values. Refer to the main body text for details.

REDUCE({ 1, 2, 3 }, (A, V) -> IF(A > V, V, A))REDUCE({ 1; 2; 3 }; (A; V) -> IF(A > V; V; A))

Returns the largest element of the given array. This REDUCE formula does the same thing as the MAX function, except for the fact that it does not filter out blank values. Refer to the main body text for details.

REDUCE({ 1, 2, 3 }, (A, V) -> IF(A < V, V, A))REDUCE({ 1; 2; 3 }; (A; V) -> IF(A < V; V; A))

Returns the smallest element of the given array. This REDUCE formula does the same thing as the MIN function, except for the fact that it does not filter out blank values. Refer to the main body text for details.

REDUCE({ TRUE, FALSE, TRUE }, (A, V) -> IF(A, V, FALSE), TRUE)REDUCE({ TRUE; FALSE; TRUE }; (A; V) -> IF(A; V; FALSE); TRUE)

Returns TRUE only if all array elements are TRUE. This REDUCE formula does the same thing as the AND function, except for the fact that it does not filter out blank values. Refer to the main body text for details.

REDUCE({ TRUE, FALSE, TRUE }, (A, V) -> IF(A, TRUE, V), FALSE)REDUCE({ TRUE; FALSE; TRUE }; (A; V) -> IF(A; TRUE; V); FALSE)

Returns TRUE if one or more arrays elements are TRUE. This REDUCE formula does the same thing as the OR function, except for the fact that it does not filter out blank values.

REDUCE({ TRUE }, (A, V) -> IF(V, A + 1, A), 0, A -> ISODD(A))REDUCE({ TRUE }; (A; V) -> IF(V; A + 1; A); 0; A -> ISODD(A))

Returns TRUE if an odd number of array elements are TRUE. This REDUCE formula does the same thing as the XOR function, except for the fact that it does not filter out blank values. Refer to the main body text for details.

REDUCE({ "short", "long" }, (A, V) -> MAX(LEN(V), A), 0)REDUCE({ "short"; "long" }; (A; V) -> MAX(LEN(V); A); 0)

Returns the number of characters in the longest word given as elements of the array passed as the first parameter. Refer to the main body text for details.