Advanced Excel Sample Assessment Instructions
• You may add extra columns if you wish.
• Use absolute referencing where possible.
• Format consistently and appropriately.
Open the file Student Records.xlsx. This spreadsheet is used to record and manage student results and loans.
This spreadsheet contains 3 worksheets.
PART 1
Use the Marks worksheet.
This worksheet lists the marks students have received over 3 years of study. For each year, a mark for English is recorded and a mark for their other studies (Course Mark). It also shows how many units were credited from a student’s previous studies.
1. Insert a column to the right of Column H (YEAR 3 ENGLISH) and add the column heading FINAL ENGLISH MARK. Use a function to calculate the average of the three English marks, showing it with 0 decimal places. Bold these values.
2. Add the column heading FINAL COURSE MARK to the right of Column L (YEAR 3 COURSE MARK). Use a function to calculate the average of the three Course marks, showing it with 0 decimal places.
•
Add the column heading FINAL RESULT to the right of Column M. Use a function in this column to show either Pass or Fail. Refer to the conditions below and use absolute referencing.
Show Pass if
▪ a Commerce student receives a Final English Mark of 70% or more, and a Final Course
Mark of 80% or more.
▪ a student from any other degree receives a Final English Mark of 70% or more, and a Final
Course Mark of 60% or more Otherwise show Fail
3. Refer to the Students Statistics area at the bottom of the spreadsheet. In the cells shaded yellow, use the correct functions to:
a. C39 – Count the number of male students
b. C40 – Count the number of female engineering students
c. C44 – Show the highest Final English Mark
d. C45 – Show the full name of the student who gained this mark – you may do this in two steps. NOTE: you need to use a function to show the name in this format:
Last name, First name (example: Nguyen, April)
Advanced Excel, Sample Assessment, Page 1 of 3
PART 2
Use the Loans worksheet.
The purpose of this worksheet is to show the amount a student needs to repay on money they have
borrowed for their study fees.
The sheet lists how much they have paid (Amount Already Paid) and the date this was paid (Date Paid).
1. Insert a column to the right of the Student column and add the column heading Full Fees. Use a function to show the fees that each student needs to pay, based on the degree they are
enrolled in. Refer to the table below the spreadsheet.
2. Insert a column to the right of the Date Paid column and add the column heading Days Late.
Use a function to show how many days late the payment was. Cell B2 shows the date that the fees were due. If the result is a negative number, show $0.
3. Calculate the Late Fee, which is $200 per day late, as shown in cell A49. Use absolute referencing.
4. Insert a column to the right of the Late Fee column and add the column heading Loan Period (years). The Loan Period differs depending on the Degree. Use a function to show this, referring to the table below the spreadsheet.
5. Use a function to show the Monthly Repayment amount. This must take into consideration the Amount Already Paid, the Late Fee, and the Interest Rate.
6. Insert a column to the right of the Monthly Repayment column and add the column heading Warning.
Use a function to check if the day of the week that the instalment was paid (Date Paid) is a Friday, Saturday or Sunday. If it is, then show the message “Check payment”, otherwise show a blank.
PART 3
Use the Consultation worksheet.
This worksheet shows the numbers of students from each specialisation who attended consultation each
month. It also shows the number who logged in to an online session.
1. Create a chart similar to that shown on the next page. Move it to a chart sheet with the name
Consultation Chart.
Pay careful attention to data selected, titles and formatting. The chart colours may be different on
your computer – this is not important.
Advanced Excel, Sample Assessment, Page 2 of 3
Advanced Excel, Sample Assessment, Page 3 of 3