Arrays & ARRAYFORMULA › ARRAY_CONSTRAIN, SORT47 / 67

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

MediumFreeARRAY_CONSTRAINSORT

ARRAY_CONSTRAIN: trim a result to the top 3

The manager of a phone accessories shop in Medan shows a "Top 3 cashiers this week" board on the shop screen. The data has four columns, but the screen only fits the cashier name and total sales.

Task

In F2, write one formula: sort A2:D8 by total sales (column 2) from highest with SORT, then trim the result with ARRAY_CONSTRAIN so that only 3 rows and the first 2 columns (cashier and total) appear in F2:G4.

Cells to fillF2:G4

Required functionsARRAY_CONSTRAINSORT

Expected result

Loading the expected result…

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

Hint 1
ARRAY_CONSTRAIN(array, num_rows, num_cols) trims an array result starting from the top-left corner.
Hint 2
Sort first with SORT(A2:D8, 2, FALSE).
Hint 3
Wrap that SORT in ARRAY_CONSTRAIN, then keep 3 rows and 2 columns.

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

=ARRAY_CONSTRAIN(SORT(A2:D8,2,FALSE),3,2)

Explanation

SORT ranks the seven cashiers from the highest sales, then ARRAY_CONSTRAIN(…,3,2) keeps only the top 3 rows and the first 2 columns: Taufik, Vino and Rudi with their totals. Columns are always dropped from the right, so put the columns you want to show on the left. If the data only has two columns, SORTN(A2:B8,3,0,2,FALSE) is shorter.

Starting worksheet: Sheet1
Row	A	B	C	D	F	G
1	Cashier	Total sales	Transactions	Shift	Cashier	Total sales
2	Putri	8,450,000	112	Morning
3	Rudi	9,120,000	98	Afternoon
4	Sinta	7,300,000	130	Morning
5	Taufik	10,250,000	105	Night
6	Umi	6,900,000	87	Afternoon
7	Vino	9,800,000	120	Night
8	Wina	8,800,000	101	Morning
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result