Course outline / Advanced Excel
Lesson 65 of 75 · Intermediate · 4 exercises
HR data analysis
Length of service, anniversaries and salary bands from staff records.
What you'll practise
- Years of service
- Next work anniversary
- Salary band
- Total payroll
Years of service
In cell H2, work out Ana's whole years of service, from her start date (E2) to the date in G2, with DATEDIF.
Next work anniversary
In cell H2, find Ana's next work anniversary: her start date moved on by (years of service + 1) × 12 months, with EDATE.
Salary band
In cell H2, put Ben's salary (D3) in a band with IFS: "High" from 55,000, "Mid" from 45,000, otherwise "Entry".
Total payroll
In cell H2, work out the total monthly payroll: all salaries in D2:D6 divided by 12.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.