ICAEW.com works better with JavaScript enabled.
Exclusive

Excel tips & tricks

Excel Tips & Tricks #474 - How to REDUCE repetitive calculations to a single step

Lego graph image

Author: Thomas Edmunds

Published: 09 Jun 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to Excel Tips and Tricks! This week, we have a Creator level post in which we explore how to use the REDUCE function to perform repetitive calculations in a single step.

REDUCE is a relatively new function, having been introduced in 2021, and is available to users of Office 365. The basic idea is that it takes an initial value, then performs a calculation for each value in a target array then returns the output as a single value.

Why is that useful, you may ask? Well, broadly speaking REDUCE can be useful in the following scenarios:

  1. Aggregating numeric values that meet certain criteria.
  2. Cleaning up text data by removing unwanted characters.

The logic that underpins that accumulation is provided as a LAMBDA function. Don’t worry if you’ve never seen or heard of LAMBDA functions before! For this article we are not assuming any prior knowledge of LAMBDA functions, just a basic familiarity with building formulas in Excel – though if you do want to learn more, we covered LAMBDA functions in Tip #459.

The main concept here to understand is the chain of calculations that underpins the calculation. We will build the intuition first by way of examples before digging into the syntax of the REDUCE and LAMBDA functions.

Example 1 – adding a list of numbers

In the first example we are simply taking our initial value and adding on all the values in the target array, one by one, until we get our final output.

Screenshot from an Excel spreadsheet

On the right-hand side, we are showing how the same result can be achieved in a single REDUCE function.

This is obviously a trivial example; in real life applications you would simply use the SUM function for this task. But by following the step-by-step logic at this stage we set the scene for more advanced applications of the REDUCE function.

Example 2 – adding numbers using custom criteria

In the second example we are looking to add all the even numbers in our target array (by using a combination of the IF and ISEVEN functions). This is not something that can be achieved with standard applications of the SUM or SUMIF functions.

Screenshot from an Excel spreadsheet

As you can see the final output is the sum of the even numbers in the list: 2 + 4 = 6.

Here we are starting to tap into the power of the REDUCE function by using it in combination with the IF function.

Example 3 – text clean-up operations

The final example (before we dig into the syntax of the REDUCE function) considers text instead of numbers. Here we are looking to clean (i.e., remove) the characters in the target array from the initial value by repeated application of the SUBSTITUTE function:

Screenshot from an Excel spreadsheet

Here we have an initial value containing characters that we don’t want (#%<>). The REDUCE function removes each of these in turn leaving the desired output without just the clean text.

REDUCE function in detail

Here we take a closer look at the syntax of the REDUCE function:

= REDUCE ( initial value , target array , LAMBDA function )

Taking each argument in turn:

initial value
The initial value is exactly that. This is the starting point for your calculations.

target array
The target array is the range of cells containing the values that will be passed one by one into our calculation.

LAMBDA function
This is where the magic happens! The syntax for the LAMBDA function is as follows:

= LAMBDA ( first parameter , second parameter , calculation )

  • The first parameter will keep track of the calculated value that is passed from one calculation to the next (see the intermediate values in column E in the examples above). We can choose any name we want for this parameter, so let’s keep things simple and choose the letter “a”.
  • The second parameter allows you to refer to each value of your target array in turn. Again, we can choose any name, but for simplicity let’s choose the letter “b”.
  • Finally, we build the calculation with any valid Excel formula. We can refer to the first and second parameters above, using the names we have chosen, “a” and “b”. The calculation will be performed for each value in the target array, building one long chain of calculations from the initial value to the final output of the reduce function.

Why REDUCE?

In summary the REDUCE function can help you to reduce a set of values into a single value. By using the power of LAMBDA, you can simplify tasks that may previously have taken many repetitive calculations to complete.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.