Worked example: Cleansing and Analysing a Client Data Set

What this is

A complete, secure Year 11 answer, annotated so you can see which success criterion each part meets. It is also the answer key. There are 13 problems in the raw data; the four blank ratings are not problems. Key results on the cleaned data: Comedy 10, Horror 7, Drama 4, Sci-fi 4, Animation 3, No answer 1; 20 people aged 11 to 18, of whom 13 (65%) heard via TikTok; mean maximum price £5.28 overall, £4.60 for 11 to 18, £7.00 for 19 and over; mean rating 4.08 from 24 ratings; mean age 26.2 raw and 21.75 cleaned. Every figure has been recomputed from the data. Practice only: this is not NEA work.

Step 2: the cleansing log

Row	Column	Value now	Technique	Change it to, and why
4	C town	Mtn Ash	Use standard format	Mountain Ash, so filters and counts group it with the other Mountain Ash rows.
6	E genre	horror	Use standard format	Horror. COUNTIF ignores capitals, but a consistent format keeps filters, sorting and charts tidy.
10	E genre	Comedey	Correct error	Comedy. Misspelt, so COUNTIF(…,"Comedy") would miss it.
12	F time	6pm	Use standard format	18:00, the 24-hour format in the rules.
13	B age	142	Remove outlier	Impossible: breaks the 11 to 99 rule. It could be 14 or 42, so do not guess. Delete the value, keep the rest of R12’s answers, and leave R12 out of any age analysis.
17	G price	£7	Use standard format	7, stored as a number. As text, AVERAGE ignores it without warning.
18	I rating	7	Remove outlier	Breaks the 1 to 5 rule and the true rating cannot be known. Delete the value and add a note, because R17 has visited, so the blank does not mean “not visited”.
21	E genre	(blank)	Insert missing value	No answer. The person did answer the rest; do not invent a genre, but make the gap visible so the genre counts still add up to 29.
23	whole row	R07 again	Remove duplicate	Delete the row. It is identical to row 8. The brief says 29 people; there are 30 rows.
25	E genre	Sci fi	Use standard format	Sci-fi, the spelling in the rules.
26	C town	Aberdâr	Use standard format	Aberdare. Same town (Aberdâr is the Welsh name), but the rules use the English names so the data filters together.
27	G price	four	Correct error	4, as a number. As text, AVERAGE ignores it.
28	F time	8pm	Use standard format	20:00.

(b) No. R04, R10, R14 and R22 all made 0 visits in the last 3 months, and the rules say the rating is left blank on purpose for them. Inserting a rating would invent an opinion about a cinema they have not been to. AVERAGE ignores blank cells, so the mean rating is still correct.

Every problem is tied to a rule in the brief and a named technique. Where the true value cannot be known (rows 13 and 18) the value is removed, not guessed, and the rest of the row is kept. It also recognises when a blank is correct. Evidences criterion 1.

Step 3: validation rules

Column	Validation setting	Error alert message
B age	Allow: Whole number, between 11 and 99	Age must be a whole number from 11 to 99.
E favourite_genre	Allow: List, source Animation, Comedy, Drama, Horror, Sci-fi (gives a drop-down list)	Choose a genre from the list. If they did not answer, leave it blank.
G max_ticket_price_gbp	Allow: Whole number, between 0 and 15	Type the price in whole pounds from 0 to 15, with no £ sign.
I rating_1_to_5	Allow: Whole number, between 1 and 5, with “Ignore blank” ticked	Rating must be 1 to 5. Leave it blank if they have not visited.

Each rule matches a known standard in the brief, and the alert tells the volunteer how to fix the entry, not just that it is wrong. A drop-down list would also have prevented the Comedey, horror and Sci fi errors. Evidences criterion 2.

Step 4: analyse the clean data

Question	Formula	Result
(a) Genre counts	=COUNTIF(E2:E30,"Comedy"), and the same for each genre and for “No answer”	Comedy 10, Horror 7, Drama 4, Sci-fi 4, Animation 3, No answer 1. Total 29.
(b) 11 to 18 year olds, and how many heard via TikTok	=COUNTIF(B2:B30,"<=18") =COUNTIFS(B2:B30,"<=18",H2:H30,"TikTok")	20 people. 13 heard via TikTok: 13 ÷ 20 × 100 = 65%.
(c) Mean maximum ticket price	=AVERAGE(G2:G30) =AVERAGEIF(B2:B30,"<=18",G2:G30) =AVERAGEIF(B2:B30,">=19",G2:G30)	Everyone: 153 ÷ 29 = £5.28. 11 to 18: 92 ÷ 20 = £4.60. 19 and over: 56 ÷ 8 = £7.00.
(d) Mean rating	=AVERAGE(I2:I30) and =COUNT(I2:I30)	98 ÷ 24 = 4.08, from 24 valid ratings.
(e) Start time, 11 to 18	=COUNTIFS(B2:B30,"<=18",F2:F30,"18:00"), and the same for 19:00 and 20:00	18:00: 8. 20:00: 7. 19:00: 5.

(f) Raw data: 786 ÷ 30 = 26.2, which is where Dafydd’s 26 comes from. Cleaned data: 609 ÷ 28 = 21.75. The raw mean is too high for two reasons: the impossible age of 142 is more than twice the age of the oldest real respondent, 65, and the duplicate counts a 35 year old twice. Even the cleaned mean is pulled up by a few older people: the median age is 16, and 20 of the 28 people with a known age are 18 or under. So the typical person in this survey is a teenager, not a 26 year old.

Each result has the formula that would produce it in the spreadsheet, uses functions from all levels (COUNTIF, AVERAGEIF, COUNTIFS), and shows the division so it can be checked. Part (f) proves why cleansing matters by comparing raw and clean results, and checks that the answer makes sense. Evidences criterion 3.

Step 5: the chart

A bar chart suits this data because the categories are separate, with no order, and the reader needs to compare counts. The bars make it obvious that TikTok is more than 4 times bigger than any other channel. A pie chart would make 2 and 3 hard to tell apart and cannot show the 0 for Facebook.

Title, labelled axes and values, and the chart shows only the group the question asks about. The choice is justified by the type of data. Evidences criterion 4.

Step 6: from findings to success criteria

	Finding, with a figure	Success criterion for the website
1	13 of the 20 people aged 11 to 18 (65%) heard about the cinema via TikTok. None heard via Facebook.	Every page has a link to the cinema’s TikTok account in the header, and the home page shows the latest TikTok trailer.
2	Comedy (8) and Horror (6) are the favourites of 14 of the 20 young people (70%).	The What’s On page can be filtered by genre in one click, and lists at least one comedy or horror screening each month.
3	11 to 18 year olds would pay £4.60 on average, against £7.00 for people aged 19 and over.	The home page shows a ticket price for 11 to 18 year olds of no more than £4.60, visible without scrolling.

Not clearly supported: the best start time. Among 11 to 18 year olds, 18:00 has 8 votes, 20:00 has 7 and 19:00 has 5. A difference of one person out of 20 is too small to plan around. The committee should ask again in a larger survey before moving any screening times.

Each finding carries a figure, and each success criterion can be checked as met or not met by a tester. The last answer shows the limits of a small sample and changes the conclusion to match. Evidences criterion 5.

If you finish early: answers

First add a helper column J with a nested IF: =IF(B2="","Unknown",IF(B2<=18,"11 to 18","19 and over")). Without the first test, R12’s blank age counts as 0 and is wrongly put in the 11 to 18 group. Pivot table: Rows = heard_about_us_via, Columns = the age group in J, Values = Count of id.
People in the foyer already come to the cinema, and one school gives one age group from one area. The people the cinema most needs to hear from, young people who never come, are missing. A fairer survey would be an online form shared through several schools and youth clubs in the valley, as well as in the foyer.
Keep the file in the cinema’s own account, shared only with the committee members who need it, never with a public link. Store no names or contact details with the ages, and delete the survey once the project is finished.

Why this response is secure

It cleanses against the client’s own rules and never invents data.
It prevents the same errors next time with validation, not just fixes them this time.
Every statistic comes with the formula that produces it, and the results are sense-checked.
It shows why cleansing matters: the raw mean age is wrong by more than 4 years.
The findings are about the target audience, carry figures, and turn into measurable success criteria, with an honest limit.