Challenge Scenario
In Human Resources (HR) administration and employee benefits tracking, company employees receive a standard annual Paid Time Off (PTO) allowance of 15 vacation days per year.
HR managers monitor PTO requests to calculate remaining vacation balances and ensure that no team member exceeds their authorized allowance.
As an HR Operations Specialist, your tasks are:
- Days Remaining (Column D): Subtract Days Taken from Annual Allowance (
Annual Allowance - Days Taken).
- Leave Status (Column E): If Days Remaining is negative (
D2 < 0), assign "OVER LIMIT"; otherwise assign "OK".
- Total Days Taken (Cell C7): Sum all vacation days taken across the team using
SUM(C2:C5).
- Over Limit Employees Count (Cell E8): Count how many employees exceeded their PTO limit using
COUNTIF(E2:E5, "OVER LIMIT").
Your Sheet Layout
Understanding how the employee PTO tracking sheet is organized:
- Employee Vacation Balances (Columns A to C): Shows employee names, standard annual allowance (15 days), and actual days used.
- Balance Calculation & Status (Columns D & E): Calculates remaining vacation days (
Allowance - Taken) and flags over-limit employees.
- Department Leave Summary (Rows 7 & 8): Aggregates total days taken across the team (52 days) and counts policy violations (1 employee).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Employee Name, Allowance, Days Taken |
Leave Records |
Annual allowance and used days |
Read-only baseline employee PTO records. |
D2:D5 |
Days Remaining |
Calculation |
=B2 - C2 |
Calculates remaining vacation days for each staff member. |
E2:E5 |
Leave Status |
Policy Status |
=IF(D2 < 0, "OVER LIMIT", "OK") |
Flags employees who took more days than allowed. |
C7 |
TOTAL DAYS TAKEN |
Team Summary |
=SUM(C2:C5) |
Calculates total vacation days taken by the team (52). |
E8 |
OVER LIMIT EMPLOYEES |
Team Summary |
=COUNTIF(E2:E5, "OVER LIMIT") |
Counts employees exceeding their vacation allowance (1). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate Days Remaining by subtracting Days Taken from Annual Allowance (=B2-C2).
2
In Column E (E2:E5), assign Leave Status using IF: If Days Remaining < 0, assign "OVER LIMIT", else "OK".
3
In cell C7, calculate total days taken using SUM(C2:C5).
4
In cell E8, count over limit employees using COUNTIF(E2:E5, "OVER LIMIT").
Solution & Formula Breakdown
Let's examine how each calculation and policy check works step by step:
1. Calculating Days Remaining (D2:D5)
=B2 - C2
Subtract Days Taken in Column C from Annual Allowance in Column B:
- David Miller (Row 2):
15 - 8 = 7 → 7 days remaining.
- Jessica Alba (Row 3):
15 - 17 = -2 → -2 days remaining (deficit).
- Robert Chen (Row 4):
15 - 12 = 3 → 3 days remaining.
- Samantha Fox (Row 5):
15 - 15 = 0 → 0 days remaining.
2. Checking Leave Compliance with IF (E2:E5)
=IF(D2 < 0, "OVER LIMIT", "OK")
If Days Remaining is negative (D2 < 0), output "OVER LIMIT"; otherwise output "OK".
3. Team Summary Rollups (C7 & E8)
- Total Days Taken (Cell C7):
=SUM(C2:C5) adds 8 + 17 + 12 + 15 = 52 days.
- Over Limit Employees (Cell E8):
=COUNTIF(E2:E5, "OVER LIMIT") returns 1 employee.
In arithmetic subtraction, simple relative references (=B2-C2) are used so that copying down to row 3 automatically calculates =B3-C3 without needing absolute dollar signs.