9. Dynamic Arrays

9.19 MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY with Real Examples

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Amir wants a running total of the 10 orders, so he can see when sales crossed ₹1,000 and ₹2,000. The old way is a formula like =SUM($G$2:G2) copied down. With SCAN one formula spills the whole running total. SCAN belongs to a family of six "helper" functions. Each one takes a LAMBDA and runs it over an array in a different way. Once you know which helper walks the data how, you can solve many problems with one formula.

First look at the data (before → after):

Expected result — J2 spills the running total

J2: =SCAN(0,G2:G11,LAMBDA(acc,v,acc+v))
acc = the total so far (starts at 0)   v = the current amount   acc+v = the new total

Which helper walks the data how?

MAP: a 10% late coupon. Orders that took more than 15 minutes get 10% off. MAP walks Amount (G) and Mins (H) side by side:

=MAP(G2:G11,H2:H11,LAMBDA(a,m,IF(m>15,a*0.9,a)))

Total after coupons: =SUM(MAP(G2:G11,H2:H11,LAMBDA(a,m,IF(m>15,a*0.9,a)))) = 2707.9. (Here a simple =IF(H2:H11>15,G2:G11*0.9,G2:G11) would also work; MAP becomes necessary when the calculation per value needs functions that do not spill, such as AND, OR or a named LAMBDA that works on one value at a time.)

REDUCE: one final answer.

The second one is a real cleaning trick: the accumulator starts as the text, and each pass removes the next unwanted piece. Add more pieces to the list in braces and the formula does not grow. Wrap it in VALUE to get the number 1299.

SCAN: every step, not just the end. Besides running totals, SCAN can track a running maximum, for example the slowest delivery so far:

=SCAN(0,H2:H11,LAMBDA(acc,v,MAX(acc,v))) returns 9, 12, 14, 14, 16, 16, 18, 18, 18, 18.

BYROW and BYCOL on the weekly grid. The fictional weekly sales (₹ thousand) for three cities are in A1:E4 (City, W1 to W4):

Why not just =SUM(B2:E4)? Because SUM over a block gives one number. BYROW gives one answer per row, as a spill that stays correct when you add a city.

MAKEARRAY: build a table from a rule. A ready-reckoner of price × quantity for three products (Milk 500 ml ₹32, Bread ₹45, Eggs (6) ₹60), quantities 1 to 5:

=MAKEARRAY(5,3,LAMBDA(r,c,r*INDEX({32,45,60},c)))

r is the row number (the quantity) and c is the column number (which price). The prices are sample practice prices.

How to choose

1. Need one answer per value? MAP.
2. Need one final number from a list? REDUCE.
3. Need every in-between step (running total, running max)? SCAN.
4. Need one answer per row or per column of a table? BYROW / BYCOL.
5. Need to create a grid from row and column numbers? MAKEARRAY.

How these results were checked: LAMBDA helper functions do not run in LibreOffice, so each expected value was computed in Python and the totals were cross-checked in LibreOffice with classic formulas (SUMIF, running SUM, SUMPRODUCT). Microsoft reference: MAP function, REDUCE function, SCAN function, BYROW function, BYCOL function, MAKEARRAY function.

Practice task (try it before you open the answer):

1. Use SCAN to show the running count of Delivered orders (Status in column I).
2. Use BYROW to show the average weekly sales per city on the weekly grid (round to 1 decimal).
3. Use MAKEARRAY to create a 3 by 3 grid where each cell shows row × 10 + column.