Case studies › IF, ROUND140 / 144

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

MediumFreeIFROUND

Case study: payslip with overtime pay

Tika, an HR officer at a garment workshop in Bandung, is preparing this month's payslips. Overtime follows the weekday rule: the hourly wage is 1/173 of the base salary, the first overtime hour pays 1.5 times and each following hour pays 2 times the hourly wage.

Task

Fill in two columns:

E2:E7 Overtime pay: use IF to separate overtime of at most 1 hour (hours × 1.5) from more than 1 hour (1.5 + (hours − 1) × 2), multiply by base salary / 173, then round to whole rupiah with ROUND.
F2:F7 Total pay: base salary + allowance + overtime pay.

Cells to fillE2:E7F2:F7

Required functionsIFROUND

Expected result

Loading the expected result…

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

Hint 1
First work out the "multiplied hours": if overtime ≤ 1 hour, hours × 1.5; otherwise 1.5 + (hours − 1) × 2. That is the IF part.
Hint 2
Multiply the multiplied hours by the hourly wage B2/173, then wrap everything in ROUND(…, 0).
Hint 3
Pattern for E2: =ROUND(IF(C2<=1, …, …)*B2/173, 0). F2 is just the sum of three cells.

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

E2:E7

=ROUND(IF(C2<=1,C2*1.5,1.5+(C2-1)*2)*B2/173,0)

Write it in E2, then fill down to E7.

F2:F7

=B2+D2+E2

Write it in F2, then fill down to F7.

Explanation

=ROUND(IF(C2<=1,C2*1.5,1.5+(C2-1)*2)*B2/173,0) for Rudi (salary Rp4,200,000, 3 overtime hours): multiplied hours 1.5 + 2 × 2 = 5.5, then 5.5 × 4,200,000 / 173 ≈ Rp133,526. Staff with no overtime automatically get 0 because 0 × 1.5 = 0. Rounding each row keeps the payslip total equal to the money actually paid. This rule covers weekday overtime; overtime on rest days uses different multipliers.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F
1	Name	Base salary	Overtime hours	Allowance	Overtime pay	Total pay
2	Rudi	4,200,000	3	300,000
3	Nita	4,200,000	0	300,000
4	Yanto	4,500,000	1	350,000
5	Lina	4,800,000	5	400,000
6	Asep	4,200,000	2	300,000
7	Desi	5,200,000	4	450,000
8
9
10
11
12
13
14

Fill in the requested cells, then press Check answer.

Validation result