MSCI 242L Coursework: Health Survey Analysis The Health Survey for England (HSE) is a series of annual surveys about the health of people living in England. The survey is sponsored by the Department of Health (DoH) to provide better and more reliable information about various aspects of people’s health and to monitor selected health targets, such as drinking, smoking and weight. A supplied datafile by the DoH provides 25 key pieces of health data, for over 9,000 individuals, including information on weight and BMI, plus activity habits relating to drinking, smoking and exercise. Part A [50 marks] 1. How many people completed the survey? 2. How many males completed the survey? 3. How many females completed the survey? 4. How many 1-person households are there in the survey? 5. How many people are married1 (Question 9 in the survey)? 6. How many males are married1 (Q9)? 7. How many households are there? 8. How many people1 have 'no qualification' (Q11)? 9. How many people earn over £150,000 (Q12)? 10. How many people rated their health as 'good' or very good' (Q13)? 11. What is the average2 age (Q8)? 12. What is the average2 age of females (Q8)? 13. What is the average2 number of people per household (Q4)? 14. What is the average2 weight of males1 (Q21)? 15. What is the average2 weight of married males1 (Q9 & Q21)? 16. What is the average2 BMI of males1 (Q22)? 17. What is the average2 BMI of females1 (Q22)? 18. What is the average2 units of alcohol per week1 (Q17) across all ages? 19. What is the median2 units of alcohol per week1 for females aged 18 years and older (Q17)? 20. What is the interquartile range2 for the number of units of alcohol per week1 for females aged 18 years and older (Q17)? 21. How many people earning £100,000 or more1 (Q12) smoke more than 20 cigarettes per day (Q15)? 22. How many females aged 18 years and older are currently non-smokers1 (Q15)? 23. How many females aged 18 years and older are currently non-smokers1 (Q15) and have never smoked1 (Q14)? 24. How many females aged 18 years and older are current smokers1 (Q14) and rate their health as ‘very good’ (Q13)? 25. What is the average2 units of alcohol per week1 for divorced males (Q17)? 1 Ignore any individuals who responded ‘don’t know’ or ‘not applicable’ or ‘refuse to answer’ to this question. 2 Round your answer to 2 decimal places, using a suitable Excel function. Do not simply format the cell to display 2 decimal places, as this will receive reduced marks. Part B [20 marks] 26. What is the age of the oldest female in the survey (Q8)? 27. What is the 8-digit pserial value (Q3) for the oldest current smoker3 (Q14) in the survey? 28. How many people does the tallest female3 (Q20) in the survey live with? 29. How many people live in the house (Q4) where the individual who drinks the most units of alcohol per week3 lives (Q17)? 30. What is the 8-digit pserial value (Q3) for the oldest divorced female3? Part C [30 marks] 31. Clearly, some strategic health authorities have more respondents than others. Limiting your analysis to only the health authorities with at least 200 respondents in the survey, which authority (by name) has the ‘best’ self-assessed general health1, on average (Q13)? 32. An alternative method of assessing health is to look at the number of respondents who rate their own general health as ‘good’ or ‘very good’ (Q13). Again, looking only at health authorities with at least 200 respondents in the survey, which authority (by name) has the highest percentage of their respondents rating their health1 as ‘good’ or ‘very good’? 33. Which household is the nearest neighbour to the household that contains the most people4, based on straight-line distances? Return the hserial value for the household. 34. How many people live in the household that has the biggest age difference between the oldest person and the youngest person in that house4, in years? 35. How many all-female households are there? 36. pserial is an 8-digit value combining the 6-digit hserial value and the persnum value, as a 2-digit value. for example, for household 1 the pserial is: 10105101 However, a concern has been raised that there may be some bugs in the data, such that some of these pserial values do not follow this rule and hence are incorrect. How many pserial values in your dataset are incorrect5? 3 if more than one individual shares this value, then present the first value that appears in the datablock 4 if more than one household shares this value, then use the first household that appears in the datablock 5 note: do not alter or change any of the data, even if you identify an error hserial persnum Important: These data are copyrighted: no use must be made of this research example or the data contained therein without express permission. Furthermore, the data have been subject to small adjustments that maintain the general validity of any patterns observed but not necessarily the precise quantities. Dr Adam Hindle © 2023 Data collection is by code number, based on your student ID number Group size for this coursework can be any size between 1 and 4 members. Each group will use a specific dataset, identified by a 2-digit code number, for example code 64.xlsx If you decide to do this coursework individually then your dataset code is the last two digits of your student ID number. For example, if your ID number is 12345678 then use dataset code 78.xlsx If you complete the coursework as a group then use the highest value for the last two digits of the ID numbers of the group members. For example, if the group has three members and their ID numbers are: 22222222, 33333333 and 44444444 then this group must use dataset code 44.xlsx If you have any questions regarding this then you must contact Adam before submission. All datasets are different, so using the incorrect dataset will generate incorrect results. All datasets are on moodle in a folder called ‘Coursework Data’ Submission Rules ✓ Deliverable is an Excel 2021 workbook addressing all the tasks ✓ The model will be assessed on a University-spec. PC-version of Excel 2021 – Mac users take note! ✓ All answers should be presented in the correct cells on the User sheet ✓ The User sheet should also include your ID numbers ✓ Only submit one model per group ✓ Answers to Q1 – Q33 must be formula or function-based i.e. not generated by sorting or filtering the data, not macro-based, not simple/static values, and not found manually by the user ✓ Q34 – Q36 may be addressed by either a formula or a macro ✓ If you use a macro for Q34 – Q36 then you must include the working VBA code in your submission ✓ Failure to comply with these rules will result in a zero mark for that task ✓ Only correct results will receive the mark. No partial marks awarded. Other Penalties: The model should not exceed 8MB in size (5 marks deducted) The number of sheets in the workbook should not exceed 5 (5 marks deducted) The model should not have any hidden sheets (5 marks deducted) The model should be free from any circular references (5 marks deducted) All sheets should have a dedicated name, i.e. not simply Sheet1 (5 marks deducted) Using the incorrect dataset for your group (10 marks deducted) Deadline for submission is: 25th April 2023
欢迎咨询51作业君
