MODULE 2 · LESSON Assessment
Practical Health Data Analysis Exercise
Estimated time: 45 minutes
Case study: Monthly Health Facility Attendance
You are supporting a fictional district health office. Four facilities submitted the following monthly attendance figures. The table is in wide format: each month is a column.
| Facility | January | February | March |
|---|---|---|---|
| Central Clinic | 120 | 135 | 110 |
| Riverside Clinic | 95 | 100 | 105 |
| Hilltop Clinic | 110 | 115 | 120 |
| Valley Clinic | 75 | 80 | 85 |
Restructure from wide to long before you summarise
Copy each month into its own row so the table has three columns only: Facility, Month, and Patients. You should have 12 records. January, February and March are values in the Month column, not extra headings. The practice workbook includes a blank long-format sheet for this step.
Tasks
- Task 1. Enter the dataset into Excel. Then restructure it from the wide month columns into the long Facility, Month, Patients table.
- Task 2. Calculate the total attendance for each facility across the three months.
- Task 3. Calculate total attendance across all four facilities for January, February and March.
- Task 4. Determine which month had the highest combined attendance.
- Task 5. Create a chart comparing the three monthly totals.
- Task 6. Write three sentences explaining what the figures show. Do not assume that increasing attendance automatically means improved quality of care.
- Task 7. Identify one possible data quality problem that could affect the analysis and explain how you would investigate it.
Check the figures
Choose an answer for each question, then check. The suggested answers stay hidden until you do.
1. What is Central Clinic's three-month total?
2. What is Riverside Clinic's three-month total?
3. What is Hilltop Clinic's three-month total?
4. What is Valley Clinic's three-month total?
5. What is the grand total for all facilities and months?
6. Which month had the highest combined attendance?
Suggested written answers
- What do the figures show?
- What is one data quality risk, and how would you investigate it?
- What does the long table look like?
Module 2 completion checklist
- Explain workbooks, worksheets, rows, columns and cells.
- Structure a dataset correctly.
- Sort and filter records.
- Explain the purpose of data validation.
- Identify missing values and potential duplicates.
- Use SUM, AVERAGE, COUNT, COUNTA, COUNTIF, SUMIF and IF.
- Calculate simple percentages and reporting indicators.
- Select an appropriate chart.
- Explain how a PivotTable summarises data.
- Interpret a small health dataset without overstating the findings.
Module 2 outcome: you should now understand the basic workflow of organising, cleaning, calculating and presenting health data in Excel.
Next: Module 3 — Digital Health Data Collection and Information Systems, where digital surveys, KoboToolbox, DHIS2 and the preparation of data from different sources will be introduced.