REDUCE function

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:

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 ->:

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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 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:

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.

Processing arrays with REDUCE

To make REDUCE return an array, consider setting the initial accumulator to an array. This formula returns the array passed to it as its first parameter:

The third parameter is set to the empty array, {}. The first time the second parameter is invoked, Accumulator is set to the empty array. The first element of the given array, { 1, 2, 3 }{ 1; 2; 3 }, is 1, and is available as Value when the second parameter is invoked for the first time.

Above, 1 is added to the accumulator array using |, which does for arrays what & does for text strings. In other words, | joins arrays together. Here, it adds an element to the end of the array. Next, 2 and 3 are also added to the end of the array, in that order.

Now consider this almost-identical formula:

Note that Value appears before the accumulator. What that means is that the formula above adds values to the beginning of the resulting array, not to the end of it. The end result is that this formula reverses the array and returns { 3, 2, 1 }{ 3; 2; 1 }.

REDUCE is flexible enough to be used to perform the same work as MAP and FILTER (see below). However, your formulas will be easier to read if you stick with MAP and FILTER.

Using iteration with REDUCE

When solving a math problem, there are two broad ways to go about doing it: a direct way, and using iteration.

The simplest possible example is probably multiplying a number by an integer. You can either use multiplication directly (say, 9 * 39 * 3), or you can iteratively add nine together three times (9 + 9 + 99 + 9 + 9). Both methods arrive at the same result, though the direct method is easier and faster.

However, it isn't always easy, or even possible, to use a direct method, meaning iteration may be your only option. The XIRR function, for instance, uses iteration to calculate its result.

Let's assume that we want to calculate the balance of a bank account after 28 days, with a starting balance of $10,000 and an (unrealistic) interest rate of one percent.

For the moment, we'll ignore the fact that using the FV function or the ^ operator would be a better idea, and use iteration instead of a direct method. In other words, we need to start with $10,000 and multiply this value by 101%. Then, we need to multiply the result by 101%, take the resulting value, multiply it by 101%, and so on.

This REDUCE formula does exactly that, and returns $13,212.91:

The initial accumulator is set to $10,000 and the second parameter multiplies the accumulator by 101%.

We get REDUCE to invoke the second parameter 28 times by passing an array with 28 elements. This is accomplished using the SEQUENCE function. Here, the elements of the first array are not important, only its size.

If we instead want to return an array containing all the intermediate values ({ 10000, 10100, 10201, ..., 13212.91 }{ 10000; 10100; 10201; ..., 13212,91 }, reflecting the balances on day one, day two, etc), we need to use this formula instead:

The formula above uses the technique explained in the preceding section. The initial accumulator is set to the array { 10000 }{ 10000 } instead of the single value 10000, and | is used to add a new element to the end of the accumulator array.

In order to calculate the element to add, we need to access the last element of the accumulator array. The formula does this using the Index value, which is the position of the element in the first array that is being processed, starting with one. As a result, INDEX(Accumulator, Index)INDEX(Accumulator; Index) returns the last element of the accumulator array, which is then multiplied by 101%.

The end result is that REDUCE returns an array, starting with the balance on day one and ending with the balance on day 28.

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:

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