Spreadsheet Practice

Google Sheets lesson 15 of 17, Sheets power tools

MAP, BYROW and LAMBDA in Sheets

Quick answer

Run your own formula over every row, with one cell and no filling down.

Key formula
=MAP(B2:B6,C2:C6,LAMBDA(r,h,IF(h>40,40*r+(h-40)*r*1.5,h*r))) (Pay with overtime for each row)
ProSheets mode11 minutes, 2 exercisesMAPBYROWSCANLAMBDA

Watch it done

1 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

LAMBDA(x, ...) is a small function you define on the spot. Combine it with MAP, BYROW or SCAN to apply it across a range, and the results spill down by themselves.

MAP(range1, range2, LAMBDA(a, b, ...)) goes through matching cells of one or more ranges, so overtime pay is one formula: pay the first 40 hours at the rate and the rest at 1.5 times. BYROW hands over a whole row at a time, and SCAN keeps a running result, which makes a running total.

It does the work of ARRAYFORMULA with IF when the rule is too long to write as one array expression.

Syntax

=MAP(B2:B6,C2:C6,LAMBDA(r,h,IF(h>40,40*r+(h-40)*r*1.5,h*r)))Pay with overtime for each row
=BYROW(B2:F6,LAMBDA(r,SUM(r)))One total per row
=SCAN(0,B2:B7,LAMBDA(a,x,a+x))Running total

Watch out for

  • The LAMBDA needs one parameter name for each range you pass to MAP.
  • Keep the cells below the formula empty, or the spill is blocked.
  • BYROW passes a whole row, so use SUM, MAX and friends inside; r*2 would not return a single value.

The exercises

  1. Pay with overtime. In D2, use MAP to calculate pay for each employee: hours up to 40 at the rate in column B, hours over 40 at 1.5 times the rate. Use B2:B6 (rate) and C2:C6 (hours) in one formula.
  2. Running total of hours. In E2, use SCAN to return the running total of hours in C2:C6, starting from 0.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

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.

  1. Pay with overtime
  2. Running total of hours