What is the average score change in this group?

Practical Assignment 2.

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). The file name of your final submission on Blackboard should be HLTH2411_A2_Firstname_ Lastname.xlsx (e.g., HLTH2411_A2_ Yulong_Gu.xlsx). Submissions that are not in the Microsoft Excel format will receive a grade of zero for the assignment. Late submissions will receive a grade of zero for the assignment.
Exercise 1. Sensitivity and specificity (2.5 points) Use Excel formulas 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 in the ‘Sensitivity_ Specificity’ worksheet in HLTH2411_A2.xlsx. Assuming the prevalence in a population for this particular cancer is reflected in the truth table, use Excel formula/function to calculate this prevalence rate (0.5 point). N.B.: use correct formulas (and 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 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 (12.5 points) Analyze a pseudo dataset collected from school counselling service and answer questions on the next page in the same Excel file. The dataset is based on a depression screening tool – Patient Health Questionnaire for Adolescents (PHQ-A1). The PHQ-A instrument is shown in Table 1. Table 1. PHQ-A (adapted from online resource) How often have you been bothered by each of the following symptoms during the past two weeks? (Please CIRCLE to indicate your answer) Not at all Several days More than half the days Nearly every day 1. Feeling down, depressed, irritable, or hopeless? 0 1 2 3 2. Little interest or pleasure in doing things? 0 1 2 3 3. Trouble falling asleep, staying asleep, or sleeping too much? 0 1 2 3 4. Poor appetite, weight loss, or overeating? 0 1 2 3 5. Feeling tired, or having little energy? 0 1 2 3 6. Feeling bad about yourself – or feeling that you are a failure, or that you have let yourself or your family down? 0 1 2 3 7. Trouble concentrating on things like school work, reading, or watching TV? 0 1 2 3 8. Moving or speaking so slowly that other people could have noticed? Or the opposite – being so fidgety or restless that you were moving around a lot more than usual? 0 1 2 3 9. Thoughts that you would be better off dead, or of hurting yourself in some way? 0 1 2 3 1 PHQ-A is based on PHQ-9 which was developed by Drs Kroenke, Spitzer, and Williams, see: Kroenke, K., Spitzer, R. L., Williams, J.B. (2001). The PHQ-9: validity of a brief depression severity measure. Journal of General Internal Medicine, 16(9), 606-613. For more info on PHQ-A, see: http://www.phqscreeners.com/.
2
Exercise 2.1 Analyze PHQ-A questionnaire responses before counselling Answer the following questions in the blank worksheet (‘PHQ-A_Findings_Pre’) by processing and analyzing the raw data in the ‘PHQ-A_Pre_Counselling’ worksheet in HLTH2411_A2.xlsx: 1. In the sample (N=250), what percentage of the questionnaire participants report their race as “White” (including those identifying with ≥2 races, one of which is “White”)? (1 point) 2. In the sample (N=250), how many questionnaire participants have answered all 9 PHQ-A questions (Q1 – Q9)? (1 point) Among these participants who have completed Q1 – Q9, please answer the following questions (a-b): a. Calculate the total PHQ-A score for each participant in this group (n<250) in the ‘PHQ-A_Pre_Counselling’ worksheet ‘Total’ column. This total PHQ-A score is calculated by adding up responses to Q1 – Q9. Answer in the ‘PHQA_Findings_Pre’ worksheet: what is the average of the total PHQ-A scores in the group? (1 point) What is the median of the total PHQ-A score? (1 point) b. Identify the depression profile for each participant in this group (n<250) in the ‘PHQ-A_Pre_Counselling’ worksheet ‘Depression’ column. Criteria: a total PHQ-A score of <5 indicates that the participant has no depression symptom, total score of 5-9: mild depression, 10-14: moderate depression, 15-19: moderately severe depression, and ≥20: severe depression. Present their depression profiles in a chart. For instance, you may use a pie chart to show the percentage of participants in each of the five categories (i.e., 1. no symptom, 2. mild symptom, 3. moderate symptom, 4. moderately severe symptom, and 5. severe symptom). (2 points) 3. In the sample (N=250), what is the total number of female + male adolescents (aged 12 – 19) who have completed Q9? (1 point) Among these participants (n<250), identify the self-harm risk for each participant in this group. Criteria: the response to Question 9 indicates self-harm risk, as Q9 score of 0 or 1 indicates low risk, score 2: medium risk, and score 3: high risk. Now answer the following questions (a-b): a. Which statistical test would you use to see if there is a significant difference in selfharm risk between the two genders? (1 point) b. Does your test result (showing P value) indicate a statistically significant difference in self-harm risk between male and female participants? (2 points) Exercise 2.2 Compare PHQ-A total scores from before counselling to after

Answer the following questions in the blank worksheet (‘PHQ-A_Findings_Post’) using both the pre-counselling data (on the ‘PHQ-A_Pre_Counselling’ worksheet) and the post-counselling data (on the ‘PHQ-A_Post_Counselling’ worksheet):

1. Calculate the number of questions each participant answered in PHQ-A pre- and post- counselling questionnaires in the ‘PHQ-A_Post_Counselling’ worksheet ‘# Q answered – Pre’ and ‘# Q answered – Post’ columns. Then use these two new variables to answer this question: how many participants have completed all 9 PHQ-A questions (Q1 – Q9) in the pre-counselling questionnaire as well as in the post-counselling questionnaire? (1 point)

2. Considering only these complete responses, please calculate the within-subject total PHQ-A score change from pre-counselling to post-counselling for each participant (score change = pre-counselling total score – post-counselling total score). What is the average score change in this group? (1 point) Does this mean on average people got better or worse