Looping With MAP and REDUCE

Sometimes you want to apply the same calculation to every value in a range, or boil a whole range down to a single number. In programming this is a loop. In modern spreadsheets, MAP and REDUCE do this for you using LAMBDA.

• MAP transforms each element of an array into a new element
• REDUCE combines all elements into one accumulated result

Both take a LAMBDA that describes what to do with each value. They are available in Microsoft 365 and Google Sheets.

MAP walks through an array and applies a LAMBDA to each item, producing a new array of the same size. The LAMBDA's parameter receives one element at a time.

The pattern is: give MAP the array, then a LAMBDA whose parameter stands for each value. MAP spills the transformed results across cells automatically.

Suppose A2:A6 holds five prices and you want each one increased by 10 percent. MAP applies the markup to every value at once.

The LAMBDA parameter p represents each price in turn. MAP returns five results, one per input cell, spilling down the column.

No dragging the fill handle, no copying. One formula handles the whole range.

MAP can walk through two arrays in parallel. The LAMBDA then takes two parameters, one from each array, matched position by position.

Here we multiply quantities in A2:A6 by prices in B2:B6 to get line totals. The parameter q pairs with each quantity and pr with each price.

Both arrays must be the same size or you will get an error.

REDUCE collapses an array into a single value. It carries an accumulator that updates with each element.

It takes three things: a starting value for the accumulator, the array, and a LAMBDA with two parameters, acc (the running total) and value (the current element). The LAMBDA returns the new accumulator each step.

Let's sum a range with REDUCE (yes, SUM does this too, but it shows the mechanics clearly).

We start the accumulator at 0. For each value in A2:A6, the LAMBDA adds it to acc. After the last element, acc holds the total.

If the values were 10, 20, 30, the accumulator would step 0, 10, 30, 60, ending at 60.

Change the operation and REDUCE does something new. To multiply all values together, start the accumulator at 1 and multiply each step.

This computes a product, useful for things like compound growth factors. The starting value matters: use 0 for sums and 1 for products, since those are the neutral starting points.

You can transform with MAP and summarize with REDUCE in one formula. A common task is summing the squares of a range.

Here MAP squares each value, then REDUCE adds them up starting from 0. The MAP result becomes the array that REDUCE consumes.

Both functions exist in Google Sheets with identical syntax. MAP applies a LAMBDA to each element; REDUCE accumulates with a starting value, an array, and a two-parameter LAMBDA.

Sheets also offers related helpers like SCAN, which is like REDUCE but returns every intermediate accumulator instead of just the final one. Excel has SCAN too.

A few things to remember:

• In MAP, the LAMBDA must return one value per element
• In REDUCE, the LAMBDA must always return the new accumulator, never forget it
• Pick the right start value: 0 for sums, 1 for products, an empty string for text joins
• Arrays passed together must share the same dimensions

When in doubt, test your LAMBDA inline on a single value first.

Reach for MAP when you want a result for each input, like applying a markup or formatting every value.

Reach for REDUCE when you want to fold many values into one, like a custom total, a concatenation, or a maximum found through your own logic.

Together they let you express loops that older spreadsheets simply could not do in a single formula.

Test your understanding of REDUCE.

You learned to loop over arrays with LAMBDA-powered functions.

• MAP transforms each element into a new element, returning an array
• REDUCE folds an array into one value using an accumulator
• Choose a sensible start value: 0 for sums, 1 for products
• MAP can pair multiple arrays; combine MAP and REDUCE for powerful one-line summaries
• SCAN shows every intermediate step

Next you will save your LAMBDAs as named functions for clean, reusable formulas across the workbook.