1                                          Submitted by T.Graham, D. Dowdie, S. Edwards-Braham

Fundamentals of Information Technology

Microsoft Excel Test

Instructions:

1.   Create the spreadsheet on page2 starting from cell A1.

i.   Save it as your name excel test.  (1 mark)

ii.   Type the Heading in cell A1.  Merge and center the heading across the relevant columns. (2 marks)

iii.   Enter the data in its appropriate row (cell) (24 marks)

b.   Name the sheet original. (1 mark)

2.   Use the original sheet to accomplish the following task: -

a.   Use an appropriate formula to insert today’s date in cell B2 with the appropriate formula. (1 mark)

b.   Use absolute and relative cell referencing to calculate each coursework and exam grade for each student. (3 marks)

c.   Use an appropriate formula to calculate the total grades for each student. (2 marks)

d.   Calculate the final average for each student. (2 marks)

e.   Use a vlookup function to determine the letter grade for each student. (4 marks)

f.    In cell O4 type the heading pass or fail. (1 mark)

g.   Use an if formula to determine whether the student pass or fail. (3 marks)

h.   In cell C18 to C23, use the appropriate formula to answer the questions B18 to

B23. (6 * 2 marks)

i.    Use an appropriate column chart to display each student id number and their final grade.  Your chart must have title, axes label and legend represented. (8 marks)

3.   Copy cells A4 to N13 from the original sheet and place it on a new sheet.  Use this sheet to accomplish the following task.

a.   Name the new sheet above as sorted. (1 mark)

b.   Sort the records on the sheet in ascending order by last name. (2 marks)

c.   Create a pivot table to display all students whose id start with 999 and whose

name begins with S. (4 marks)

d.   Create a pivot chart from your pivot table. (2 marks)

e.   Use conditional formatting to show (highlight) all students who received over

90% in their final grade. (2 marks)

TOTAL MARKS 75

2                                          Submitted by T.Graham, D. Dowdie, S. Edwards-Braham

Today's

Date:

ITEC1104: Exam Analysis

30%        30%         30%                        40%

CW FIRST NAME     LAST NAME                                 STUDENT ID#           #1    #2    #3       Exam         #1

Steven               Thomas                                        999-25-5683             94    65       89           90

Suzette              Alexander                                    999-52-6938             93    91       97           80

Billy Joe             Richards                                       998-71-2838             92       1       88           90

Betty                  Rasmussen                                  997-74-4447             95    94       90           90

Hugh                  Walker                                          999-90-7878             56    78       87           90

Jen                     Hills                                               989-34-5797             76    80       60        100

Brown               Brown                                           976-90-1234             56    78    100           97

Eric                    Johnson                                        976-12-6789             87    90       80           78

Omar                 Love                                              987-34-2344             78    89       89           78

TOTAL

CW        CW

#2          #3

TOTAL

SCORE    EXAM

FINAL AVERAGE

Letter

Grade