Advanced Excel Revision Activity
In this activity you will apply many of the Advanced Excel skills you have learned so far. This will help you identify any areas you need to revise further, and help you prepare for the final stage of the Advanced Excel course: Application and Consolidation. To achieve a correct
solution:
• You may add extra columns if you wish.
• You should use absolute referencing where possible.
• You should format consistently and appropriately.
Open the file Student Records.xlsx and save it with your ZID in front of the filename. This spreadsheet contains 3 worksheets. It is used to record and manage student results and loans.
PART 1 - Marks worksheet.
The Marks worksheet lists the marks students have received over 3 years of study. For each year, a mark for English is recorded and a combined mark for their other subjects (Course Mark). It also shows the number of credits awarded 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. Apply bold formatting to 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.
3 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.
a 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
4 Refer to the Student 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)
PART 2 - Loans worksheet.
The purpose of the Loans 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.
2 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.
3 Insert a column to the right of the Date Paid column and add the column heading Days Late.
4 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.
5 Calculate the Late Fee, which is $200 per day late, as shown in cell A49. Use absolute referencing.
6 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.
7 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.
8 Insert a column to the right of the Monthly Repayment column and add the column heading Action.
9 Use a function to check if the day of the week that the instalment was paid (Date Paid). If an instalment is paid on a Friday, Saturday or Sunday, show the message “Check payment”, otherwise show the message “No Action” .
PART 3 - Consultation worksheet.
The Consultation 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.
2 Pay careful attention to data selected, titles and formatting. The chart colours may be different on your computer. The colours used and the order of the columns for each month are not important.