# 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

The initial value of the accumulator (or the
`ResultProcessor`

parameter).

## ResultProcessor

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:

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

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.

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.

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.

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.

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.

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.

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.