Computer Science 1L1: Introduction to ICT
Practical 6: Spreadsheets and Problems solving [55 marks]
Practical Information:
Hand in:
All work is to be submitted on RUConnected by 28/3/25 (23:59). If you fail to submit your work by the
deadline, you will automatically be assigned a mark of 0. You will not be able to submit your work at
a later date on RUConnected, and you are not allowed to email your work to your tutor after the deadline.
If you are worried that you will not be able to submit your work in time, submit what little you have so
that you at least get a mark. You are also encouraged to save your work repeatedly in draft form on
RUConnected before submitting the final version.
Plagiarism: By submitting this practical, you agree that the work submitted is your own and agree to
abide by the Rhodes University Plagiarism Policy
(available:https://www.ru.ac.za/media/rhodesuniversity/content/institutionalplanning/documents/Plagi
a rism.pdf ).
Please see your Computer Science departmental handbook for a friendlier version of this document. In
short, don't cheat. Cheating is lame. Only submit work that is your own, even if you worked as a group.
Discuss the problems with your friends. Figure out the solutions together. Read each other's programs.
You can (and should) help each other get your concepts straight. BUT! each of you must take those
concepts and ideas into your own mind and then be able to turn them into your OWN version of the
submission. Make sure you ultimately DO your work from what YOU understand, not from what
someone else understands. Don't give your work to another student to copy. Don't copy answers word
for word from books, notes or internet sources either. Always put things in your own words.
Marks: The total mark for this practical is 55.
Instructions: Download the "CSc1L1 SS Practical 1.xlsx" file from the RUConnected course page.
This file contains three worksheets: one for each task.
Task 1: Student Information Analysis [20]
You are provided with a dataset containing student-related information. Use formulas and
logical functions to complete the table.
1. The university wants to update the student number format. The current student
number is in Column D, and the new format should be: "G + Start Year + Initial +
Current Student Number".
o For example, Lutho Klaas' new student number would be "G21K1254".
o The new student number should be placed in Column G.
o To achieve this, first extract the student’s initial (the first letter of their
surname) and store it in Column C. [3 marks]
2. If a student has scored below 40% in any of their three subjects, they will receive a
warning.
o Use the IF function to insert "Yes" in Column K if they receive a warning;
otherwise, insert "No". [2 marks]
3. A student qualifies for the Dean’s List if they have scored 75% or higher in all three
subjects.
o Use the IF function to insert "Yes" in Column L if they are on the Dean’s List;
otherwise, insert "No". [2 marks]
4. Students who started in a year other than 2025 are considered returners, while
those who started in 2025 are freshman students (first-year students).
o Add "Returner" to Column M if the student’s start year is not 2025.
Otherwise, add "Freshman". [2 marks]
5. Residence wardens want to reward students who are on the Dean’s List and live on
campus.
o Use the IF function to insert "Yes" in Column N if a student is on the Dean’s
List and resides on campus; otherwise, insert "No".
o Note: Students in residence are referred to as "Campus". [2 marks]
6. The university plans to move struggling first-year students to campus residence for
academic support.
o Use a nested IF statement to determine which freshers need to relocate.
o A student must be relocated if they are a fresher, have received a warning,
and currently live in Oppidan.
o Add "Move" to Column O if they must relocate; otherwise, add "Stay".
[4 marks]
7. Use the Subtotal function to group students by their start year and calculate the
average mark for Subject 1 in each group.
o Hint: Sort your data before applying the Subtotal function. [5 marks]
Task 2: Workplace Injury Analysis [15]
The worksheet labeled Task 2 contains data on workplace injuries. This is a large dataset, so
you must summarize it using a Pivot Table.
• Use the Pivot Table to analyze the impact of injuries across different departments.
• Consider both incident cost and days lost to assess the effects on each
department.
• Ensure the Pivot Table also categorizes data by incident type.
• Sort the table by incident cost in descending order to identify the most affected
department.
• Format the Pivot Table using the Design tab in Excel for better readability.
• Follow the provided screenshot as a reference.
• Generate the Pivot Table in a new worksheet and name it "Pivot Table".
Task 3: Solver and Goal Seek [20]
The worksheet labeled Task 3 contains four tables with missing data. You will use Solver and
Goal Seek to complete the tables.
Ensure that the Solver Add-On is enabled in Excel. If you need assistance, contact your
tutor.
1. Loan Repayment Calculation
o Refer to Table 1.
o The table shows that under the current settings, you pay R4696 per month
for five years to repay a loan.
o Since you recently received a promotion, you decide to increase your
monthly payment to R7000.
o Use What-If Analysis to determine how many months it will take to pay off
the loan at this new rate. [5 marks]
2. Final Exam Score Requirement
o Refer to Table 3.
o Your year mark is 60%, and you want to calculate the required scores for
your theory and practical exams to achieve a distinction (75%).
o Use Solver to determine this value.
o Constraint: Ensure the calculated score does not exceed 100. [5 marks]
3. Error in Cell K8
o Why does Cell K8 display a hash symbol (#ERROR, ####, etc.)?
o Write your answer in Cell H10. [5 marks]
4. Error Messages Analysis
a. Cell J20: Explain why the error message appears. Write your answer in Cell L20.
b. Formula Copy Error: Copy the formula in Cell N9 to Cell M4. Why does it result in
an error? Write your answer in Cell P3. [5 marks]
