What is the REDUCE Function in Excel 365?

—

by

Himanshu
in

The REDUCE function is a Lambda helper function introduced in Excel 365. It allows you to loop through an array, applying a formula to each element, and accumulate a single result (like a running total or combined value).

Think of it like a fold or accumulator function in programming — it starts with an initial value and “reduces” an array step-by-step using logic you define.

🔧 Syntax

excelCopyEdit=REDUCE(initial_value, array, lambda(accumulator, value))
ArgumentDescription
initial_valueThe starting value (can be 0, “”, etc.)
arrayThe array you want to process
lambdaA custom formula with two parameters: accumulator (running total) and value (current array element)

✅ Examples of REDUCE

🔹 Example 1: Sum All Numbers in an Array

excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + v))
  • A1:A5 contains {10, 20, 30, 40, 50}
  • Output: 150

🔁 Starts with 0, then adds each value:
0 + 10 → 10 + 20 → 30 + 30 → 60 + 40 → 100 + 50 = 150

🔹 Example 2: Concatenate All Text Values

excelCopyEdit=REDUCE("", A1:A4, LAMBDA(a, v, a & v))
  • A1:A4 contains: {"Hi", " ", "there", "!"}
  • Output: "Hi there!"

🔹 Example 3: Count Values Greater Than 50

excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + IF(v > 50, 1, 0)))
  • If A1:A5 = {40, 55, 60, 30, 80}
  • Output: 3 (since 55, 60, and 80 > 50)

🔹 Example 4: Multiply All Values

excelCopyEdit=REDUCE(1, A1:A4, LAMBDA(a, v, a * v))
  • A1:A4 = {2, 3, 4, 5}
  • Output: 120

🔹 Example 5: Create a Dash-Separated List

excelCopyEdit=REDUCE("", A1:A3, LAMBDA(a, v, IF(a = "", v, a & "-" & v)))
  • A1:A3 = {Jan, Feb, Mar}
  • Output: "Jan-Feb-Mar"

🎯 Why is REDUCE Useful?

  • Performs row-by-row logic without VBA or helper columns
  • Great for cumulative totals, conditional aggregations, and string building
  • Works well inside LAMBDA-based custom functions

❓ 3 Interview-Based Questions on REDUCE

1. What is the key difference between REDUCE and SCAN in Excel 365?

(Expected Answer: REDUCE returns only the final accumulated result, while SCAN returns all intermediate steps.)

2. How would you use REDUCE to count how many numbers are even in a range?

(Hint: Use IF(MOD(value,2)=0, 1, 0) inside LAMBDA and accumulate the result.)

3. What is the purpose of the initial_value argument in REDUCE?

(Expected: It defines the starting point of the accumulation. For summing, it would be 0; for concatenating, it may be an empty string.)

Top rated products