Arrays & ARRAYFORMULA › ARRAYFORMULA, IF42 / 67

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

EasyFreeARRAYFORMULAIF

ARRAYFORMULA + IF: boarding house payment status

Mr. Joko owns a 6-room boarding house in Depok. At the start of each month he records the rent due and the amount each tenant has transferred, and wants to see right away who has not paid in full.

Task

In D2, write one ARRAYFORMULA formula with IF that fills D2:D7: write Paid if the amount received (C) is greater than or equal to the rent due (B), otherwise Short.

Cells to fillD2:D7

Required functionsARRAYFORMULAIF

Expected result

Loading the expected result…

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

Hint 1
Write IF as usual but use ranges (C2:C7, B2:B7) instead of single cells, then wrap it in ARRAYFORMULA.
Hint 2
The condition compares two ranges of the same height: C2:C7>=B2:B7.
Hint 3
The shape is =ARRAYFORMULA(IF(condition, "Paid", "Short")).

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

D2

=ARRAYFORMULA(IF(C2:C7>=B2:B7,"Paid","Short"))

Explanation

ARRAYFORMULA makes IF evaluate row by row: C2 is compared with B2, C3 with B3, and so on, and the results spill into D2:D7. Without ARRAYFORMULA, IF returns a single value and has to be copied to every row. Note the text must be exactly Paid and Short, including the capital letter. Bima transferred more than the rent, so he is still Paid because the condition is >=.

Starting worksheet: Sheet1
Row	A	B	C	D
1	Tenant	Rent due	Received	Status
2	Rina	1,500,000	1,500,000
3	Fajar	1,500,000	1,000,000
4	Gita	1,750,000	1,750,000
5	Bima	1,750,000	1,800,000
6	Ayu	2,000,000	1,500,000
7	Hendra	1,500,000	0
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result