1.
Open Practical 1 worksheet and use the appropriate formulas and functions to complete the
following tasks:
● Find the total and average score for each student. [4 Mark]
● Find the lowest and highest average score. [2 Mark]
● Use the grading table to give a grade for each student based on their average score."
[2 Mark]
2. Open Practical 2 worksheet and use the appropriate formulas and functions to complete the
following tasks:
A computing teacher wants to show google form survey creation and data analysis for her P6
students. So, she took an online survey data about digital devices for students based on their
[Link] survey data includes
❖ Student ID
❖ Gender
❖ Age
❖ Devices
The survey data are shown in Practical 2 worksheet.
Activity - 1
Answer the questions from cell J14 to J16.
● Find the total number of students in the survey list. [1 Mark]
● Find the number of students who join "Laptop". [1 Mark]
● Find the number of female students who join "Tablet". [1 Mark]
Activity - 2
In this activity, she needs to analyze data using a bar chart or column chart.
In cells G8 to G11, calculate how many students use specific digital devices.
Create a chart to show how many students use each digital device.
Give your chart a suitable title. [4 Marks]
3. Your school librarian, Mrs. Readwell needs help organizing some book information in the
school library's computer system.
She has asked you to help her using a Practical 3 worksheet to manage the library's books more
efficiently, which is a table showing details about some books in the library.
You will use the spreadsheet to help Mrs. Readwell answer questions and make the data easier to
understand.
Open Practical 3 worksheet:
a. Change the font of the entire spreadsheet to Arial, size 12 with black solid border.
[2 Mark]
b. Make the header row bold, and center-align cells E2 to G11. [1 Mark]
c. Use a formula to calculate “Income” (Borrowed × BorrowPrice) for each book?
[1 Mark]
d. In the “Stock” column, use a function to calculate the sum of “Copies Available” and
“Borrowed” for each book. [1 Mark]
e. Add a new column after “Stock” called "Remaining Copies". Use a formula to subtract
“Borrowed” from “Copies Available” for each book. [2 Mark]
f. Format the "Borrow Price" and "Income" columns to show currency (e.g., $ or £).
[1 Mark]
g. Add data validation to the 'Copies Available' and 'Borrowed' fields.
Set a rule so that only whole numbers greater than or equal to 0 are allowed. If an incorrect
value is entered, the spreadsheet should show a warning. [2 Mark]
h. Add list validation to the “Genre” field.
The list of allowed values should include: "Fiction", "Non-Fiction", "Mystery", "Fantasy",
"Science Fiction", "Romance", "Education". [2 Mark]
i. Use conditional formatting to highlight any book where Borrowed is 3 or more (these are
very popular books). [1 Mark]
j. Apply a filter to the 'Copies Available' column so that only books with more than 3 copies
available are shown. [1 Mark]
k. Sort the data table by the 'Genre' field in ascending order (A-Z). [1 Mark]
Resave the spreadsheet.