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 5,000 individuals, including information on weight and BMI, plus activity habits relating to drinking, smoking and exercise.

Part A                                    [40 marks]

Counts

1. How many people are listed in the dataset?

2. How many males are listed in the dataset?

3. How many females are listed in the dataset?

4. How many people aged under 18 are listed in the dataset?

5. How many people are married?

6. How many people aged under 18 are married?

7. How many households are there?

8. How many 1-person households are there?

9. How many 1-person households contain a person who is single?

10. How many single people live in a 2-person household?

Averages1 - see footnote 1 below

11. What is the average age of the respondents? Round the answer to 2 decimal places2 12. What is the average number of people per household? Round to 3 decimal places2

13. What is the average BMI of married people? Round down to the nearest integer2

14. What is the average BMI of married females? Round down to the nearest integer2

15. What is the median units of alcohol per week for all people 18 years or older?

Extremes

16. What is the highest observed alcohol units?

17. What is the pserial for the person with the highest observed alcohol units3?

18. What is the weight of the heaviest female, in kg?

19. What is the age of the oldest female?

20. How big is the household (size) where the oldest male4  lives?

Part B           [20 marks]

Part C               [20 marks]

Part D          [20 marks]

This section is concerned with alcohol consumption for women aged 40 and above.

Create a macro to produce a new datablock on a new sheet named ‘Part D data’ that contains only the following data: [10 marks]

•    hserial

•    sex                    (exclude all males)

•    age                    (exclude any people under 18 years old)

•    drating                (exclude any people who responded with a - 1)

How many households in the new datablock contain females aged 40 years or above with adrating above 21 units per week6? [10 marks]

 Data and Groups

Group size for this coursework can be any size between 1 and 4 members.

Once you have formed a group, submit the names of the people in your group to the 242 moodle – see the post titled ‘Coursework Group Choice’ .

Each group is identified by a group number, for example group 56.

The dataset you must use is the same as your group number, for example 56.xlsx.

If you have any questions regarding this then you must contact Adam before submission. All datasets are different, sousing the incorrect dataset will generate incorrect results.

All datasets are held on moodle in a folder called ‘Coursework Data’

 Support

Clarification of the tasks, data or wording of the questions can be done in the workshop sessions. This will not be done via email, for practical reasons due to cohort size.

 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 all your ID numbers

     Only submit one model per group

     All answers 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

     The exceptions are tasks 33 to 35 which maybe addressed by a macro but you must include the VBA macros in your submission to receive the marks

     Failure to comply with these rules will result in a zero mark for that task

     Only correct results will receive the marks. No partial marks will be awarded, unless stated otherwise

 Penalties:

You must complete this coursework individually

In addition to completing parts A, B, C and D, you must also complete the following task.

Part E - Creation of a New Datablock               [20 marks*]