Practical Assignment 3. Data Management and Analysis

Exercise objectives:
Use Microsoft Excel to further understand commonly used statistical measures in healthcare,
such as sensitivity and specificity. Practice in Excel for data management / processing, analysis,
and reporting / visualization (e.g., using tables and charts).
Exercise 1. Sensitivity and specificity (2.5 points)
Use Excel functions to calculate the sensitivity (0.5 point), specificity (0.5 point), positive
predictive value (0.5 point), and negative predictive value (0.5 point) for a cancer screening test.
The truth table of this test is on the ‘Sensitivity_ Specificity’ worksheet in GEN1266_P03.xlsx.
Assuming the prevalence in a population for this particular cancer is reflected in the truth table,
use an Excel function to calculate this prevalence rate (0.5 point).
N.B.: use the correct Functions in your submission, the result format should be in percentage
style, and don’t forget to label each measure clearly (e.g., you may fill each function calculation
in the blank cells next to the cells marked as “sensitivity”, “specificity”, “PPV”, “NPV”, and
“prevalence”, respectively, in the ‘Sensitivity_ Specificity’ worksheet).
Exercise 2. Data processing, analysis and reporting (7.5 points)
Use Excel to process, analyze, and report a pseudo dataset collected from private school
counselling services based on a validated and widely used depression screening tool – the patient
health questionnaire for adolescents (PHQ-A
1
, see also Table 1).