Logic › IF, AND41 / 144

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

MediumFreeIFAND

IF + AND: employee attendance bonus

The HR team at a garment factory in Bandung pays a Rp300,000 attendance bonus this month to employees who were present at least 22 days and were never late.

Task

Fill D2:D7 with the attendance bonus: 300000 if both requirements are met, otherwise 0. Combine IF with AND.

Cells to fillD2:D7

Required functionsIFAND

Expected result

Loading the expected result…

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

Hint 1
Two requirements must be met together. Combine them with AND, then use that result as the IF condition.
Hint 2
"Never late" means the late count equals 0, that is C2=0.
Hint 3
Pattern: =IF(AND(…, …), 300000, 0).

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:D7

=IF(AND(B2>=22,C2=0),300000,0)

Write it in D2, then fill down to D7.

Explanation

AND(B2>=22,C2=0) produces a single TRUE/FALSE, and IF turns it into the bonus amount. Teguh was present 22 days but was late once, so he gets no bonus. Common mistake: writing IF(B2>=22,C2=0,…), which puts the second requirement in the result position, so the output is TRUE/FALSE instead of an amount. Tip: if the bonus amount may change, keep it in its own cell and refer to it with $.

Starting worksheet: Sheet1
Row	A	B	C	D
1	Employee	Days present	Times late	Bonus
2	Wulan	23	0
3	Teguh	22	1
4	Mega	21	0
5	Rizal	24	0
6	Laras	22	0
7	Bayu	20	3
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result