QUERY › QUERY51 / 67

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

EasyFreeQUERY

QUERY: where with a number

Mr. Darto checks the stock of his building supply store in Solo every Saturday. He wants a list of items with fewer than 20 in stock so he can reorder them from the distributor right away.

Task

In E1, write one QUERY formula over A1:C8 that shows all columns for items with stock below 20, including the headers.

Cells to fillE1:G5

Required functionsQUERY

Expected result

Loading the expected result…

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

Hint 1
select * means all columns. The row condition goes after the word where.
Hint 2
Numbers inside the query are written as they are, without quotes, for example B < 20.
Hint 3
The pattern is QUERY(A1:C8, "select * where …", 1).

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

E1

=QUERY(A1:C8,"select * where B < 20",1)

Explanation

=QUERY(A1:C8,"select * where B < 20",1) keeps the rows with fewer than 20 in stock: cement, wall paint, plywood and ceramic tiles. Numbers are written without quotes, while text uses single quotes. If a column mixes numbers and text, QUERY uses the majority type and treats the rest as empty, so make sure the stock column holds numbers only.

Starting worksheet: Sheet1
Row	A	B	C
1	Item	Stock	Unit
2	Cement 50 kg	12	bag
3	Rebar 10 mm	45	rod
4	Wall paint 5 kg	8	can
5	2-inch nails	30	box
6	Plywood 9 mm	16	sheet
7	PVC pipe 3/4 inch	60	rod
8	Ceramic tile 40x40	19	box
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result