Spreadsheet Assignment #3: Class Average (Feedback/Formative)


SKILLS: Average Formula, Cell Formatting

In this assignment you will find the average of each of the following students and subjects.

Adjust the column widths so that all of the class titles fit as shown above. Before you type in the percentages, you should format the cells so that their number type is a percentage with no decimal places. I would also like you to format the font in the cells so that it is a 12 point Calista MT italic Blue.

Once you have added all of the information. I would like you to determine the overall averages of each of the students in  row 12 and the averages of the subjects in column I. Be sure to use the average formula when entering these values. Make the following cell formatting changes to the student averages:

  • Place a double line border around all of the cells
  • Shade the cell of each students highest average with blue
  • Shade each students lowest average with red
  • Shade the median average with yellow
  • Use shades of blue and red for the other cells – The colours should follow a continuum from dark to light according to the averages (either up or down) for each student
  • Change the font to Elephant Bold 14 point.
  • Change the colour of the font so that it is clearly legible in all cells – You may elect to have a gradient of colours for your fonts that is opposite to the shaded colours

Format the cells in the class average column according to the following:

  • Use the same font as the student averages
  • Use the same borders as the student averages
  • Fill all of the cells in this column with a tan colour
  • Select a font colour that is clearly legible with the tan background


Use two line graphs to represent the student and their overall averages, and the subjects and their overall average. Include the graphs as separate sheets and name them appropriately.

When you feel you are complete, check your letter against the checklist of requirements found below:

Does your assignment include the following?

 Adjusted column widths

 Cells are all in percentage

 Average formula for all students

 Student Average Formatting

o Double border

o Highest = Blue

o Lowest = Red

o Median = Yellow

o B/R continuum

o Elephant Bold 14 font

o New Font Colour

 Class Average Formatting

o Elephant Bold 14 font, surrounded by double border, cells have tan fill, with new font colour.

 Sorted Subjects (alphabetically)

 Line graph student

o On a new sheet , used correct info, includes title and data labels

 Line graph subject

o On a new sheet , used correct info, includes title and data labels


When you are satisfied with your assignment, hand in the completed document to the appropriate In Basket folder. (Remember to save it lastname,firstname…)