Excel Knowledge and Data Analysis Exam: Instructions and Tasks

Part A: Excel Knowledge

Part A (30 marks) covers knowledge of Excel. There will be 4 questions.
1. Resolving errors in Excel (6 marks)
You will be shown some screenshots of errors you may encounter when trying to do some analysis in Excel (e.g. a graph with the row and column variable incorrectly specified). For each of the errors shown, you need to
• explain what is causing the error, and
• describe how to correct it, or how to do the task correctly.

 

Further details on preparation and submission of data assignments
These guidelines apply to Data Assignment 1 and 2.
• Word count: The word count does not include the title of your policy report, words/numbers in figures/figure captions, words/numbers in tables/table captions, or references. Section 4.3 of the Student Handbook explains how submissions that are over or under the word count will be assessed.

• Data sources: You can use the data from lectures/seminars, and/or other data from reliable sources. ‘Reliable sources’ refers to reputable organisations such as the OECD or Our World in Data). For any data you use, you must do the charts/analysis by yourself: you cannot copy and paste charts directly from other sources, but you can download the raw data and recreate charts that other people have made.

• References: Use any referencing style of your choice, as long as you pick one style and are consistent throughout. Section 4.8 of the Student Handbook contains more details.

• Formatting: Assignments should be done on a computer (e.g. Excel and Word), use a readable font size (10pt or above), and have suitable margins (2cm or above on all sides). The submitted file should include your QMID number but not your name.

• Submission: Please keep a copy of each piece of work you submit. Sections 2.12 and 4.2 of the Student Handbook explain how late submissions will be assessed, and how to report extenuating circumstances.
• Plagiarism: Please submit an original piece of work and make sure to give appropriate credit to any sources you use. See Sections 4.7 and 4.8 of the Student Handbook for more details.

As it is an open-book exam, for questions 2-4, you may be asked about Excel formulae that are similar, but not identical, to the formulae we used in lectures and classes. You can find lists of common Excel formulae by Google-searching ‘Excel cheat sheet’ or ‘common Excel formulas’.
One example list (of 102 useful formulas) is here: https://www.exceldemy.com/microsoft-excelformulas functions-cheat-sheet/

Task 1: Resolving errors in Excel

 

Task: Make a line chart showing temperature anomalies in August from the year 2000 up to the latest available year.

2. True/false statements (6 marks)
You will be given an Excel formula and asked whether it will achieve a particular result (e.g. calculate the mean of a group of cells). If the formula has been specified incorrectly, provide the correct formula. An example is given below.

3. Explain how a particular Excel formula works (8 marks)
You will be given a screenshot of some data and some cell formulae. For each cell formula, describe what the outcome of the formula will be and explain why. If the answer is a non-integer number, round your answers to two decimal places.

4. State/Write the Excel formula needed to do a particular task (10 marks)
You will be given a screenshot of some data and need to state the cell formula needed to achieve a specific task. An example is shown below.
Note that there may be many ways to do the task, but any formula that achieves the desired result will receive marks.

Task: Divide the value in cell B2 by 2.

Part B (70 marks) covers data analysis and interpretation. There will be 3 questions.
1. Bad charts (10 marks)
You will be shown some poorly presented and/or misleading charts (example below) and asked to identify issues with the presentation of the data and suggest specific improvements, and explain how appropriate the chart is for answering the research question, and (if applicable) suggest a different representation of the same data that would be more appropriate.
Two examples of bad charts are shown below (taken from Week 2’s worksheet).

 

2. Interpret charts and tables (30 marks)
You will be given some charts and tables on a topic (for example, unemployment rates in different countries) and asked to interpret this information, explain the limitations of the data and variables used, and describe any additional information that would be helpful for understanding the topic.
Definitions and relevant information for some variables will be provided to aid your analysis.
As it is an open-book exam, you can also supplement your analysis with your own knowledge and research, as long as you acknowledge and cite all sources that you use.

 

3. Survey design (30 marks)
You will be given a questionnaire that will be used for a research study on a specified topic (e.g.
public opinion on a rise in the minimum wage). You will be asked to evaluate the usefulness of this questionnaire for collecting data that would help achieve the aim of the research study. The material from Week 6 on survey design and designing survey questions will be helpful for this section.