Lesson 31 of 45, Dynamic arrays
LAMBDA with BYROW, MAP & SCAN
Quick answer
Write your own function once and apply it to every row, pair or running step.
- Key formula
=BYROW(B2:F6,LAMBDA(r,SUM(r)))(Total of every row)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
LAMBDA(x, ...) is an anonymous function: it names its inputs and says what to do with them. By itself it does nothing; you hand it to a helper that calls it repeatedly.
BYROW(range, LAMBDA(row, ...)) runs the LAMBDA once per row and spills one answer for each, such as the weekly total of every person. MAP(a, b, LAMBDA(x, y, ...)) walks two lists in step, which suits row-by-row logic that needs an IF. SCAN(start, range, LAMBDA(total, x, total + x)) keeps a running result and returns every step: a running total in one cell.
Use these when a single plain formula can't express the row logic, or when you want the whole result to spill from one place instead of filling a column down.
Syntax
=BYROW(B2:F6,LAMBDA(r,SUM(r)))Total of every row=MAP(B2:B7,C2:C7,LAMBDA(h,rate,h*rate))Pair up two lists=SCAN(0,B2:B7,LAMBDA(a,x,a+x))Running total, step by step=REDUCE(0,B2:B7,LAMBDA(a,x,a+x))Only the final resultWatch out for
- The LAMBDA names are arbitrary but must not look like cell references. Use r, x or rate, not A1 or R2.
- BYROW gives the LAMBDA a whole row at a time; MAP gives it one value from each list.
- The result spills, so keep cells below the formula empty.
The exercises
- Weekly totals. In H2, return every person's weekly total (B to F) with one BYROW formula that spills down.
- Overtime pay. In E2, return each person's pay: hours up to 40 at the rate, and hours above 40 at 1.5 times the rate. Use MAP over the hours and the rate columns so it spills.
- Year to date. In D2, return the running total of revenue month by month with one SCAN formula.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Weekly totals
- Overtime pay
- Year to date