LET & LAMBDA › BYROW, LAMBDA114 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeBYROWLAMBDA

BYROW: gap between the best and worst week

Rangga, a sales supervisor for phone credit and data plans in Palembang, wants to see how steady each rep was over 4 weeks. The measure is simple: revenue in the best week minus revenue in the worst week.

Task

In F2, write one BYROW + LAMBDA formula that calculates, for each row of B2:E7, the largest value minus the smallest value. The result spills into F2:F7.

Cells to fillF2:F7

Required functionsBYROWLAMBDA

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
BYROW(array, LAMBDA(row, …)) sends the array to the LAMBDA one row at a time, then stacks the results downward.
Hint 2
The LAMBDA parameter holds one row (4 numbers). Functions such as MAX and MIN work directly on that parameter.
Hint 3
Pattern: =BYROW(B2:E7, LAMBDA(r, …)). Fill the last part with the MAX of r minus the MIN of r.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F2

=BYROW(B2:E7,LAMBDA(r,MAX(r)-MIN(r)))

Explanation

=BYROW(B2:E7,LAMBDA(r,MAX(r)-MIN(r))) runs MAX(r)-MIN(r) for every rep. Rina, for example, had a best week of 15.8 million and a worst of 12.1 million, so her gap is 3.7 million. The plain formula =MAX(B2:E7)-MIN(B2:E7) measures the whole table at once, not each person. Its sibling, BYCOL, works column by column, for example to get the gap for each week.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F
1	Sales	Week 1	Week 2	Week 3	Week 4	Gap
2	Rina	12,100,000	15,800,000	13,400,000	14,200,000
3	Bima	9,800,000	10,200,000	9,500,000	10,000,000
4	Citra	18,500,000	11,200,000	16,900,000	14,000,000
5	Dodi	7,400,000	8,900,000	12,300,000	9,100,000
6	Elis	13,000,000	13,600,000	12,800,000	13,300,000
7	Fikri	6,200,000	9,700,000	8,800,000	11,400,000
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result