Tag: REDUCE Function

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

Argument	Description
initial_value	The starting value (can be 0, “”, etc.)
array	The array you want to process
lambda	A 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.)

---