Friday, 17 April 2015

Ms Excel Exercise 7









Excel Assignment on Grades – Absolute reference
Create the following spreadsheet:
Student
Assign1
Assign1 %
Assign2
Assign2 %
Average %

35

55








John
34

45


Sue
31

49


Pat
28

51


Sam
22

36


Mike
18

38


Jane
7

31


Maria
29

36


Len
26

29








Average





Maximum





Minimum





  1. The numbers in B2 and D2 are the grade the assignments were marked out of. Use a formula with an absolute cell reference to calculate the assign1 % and assign2 %. These numbers are the mark the student received on the assignment divided by the mark the assignment was out of. Display the answer as a percentage with 1 decimal place 
  2. Use functions to display the average, maximum and minimum of the assign1, assign1%, assign2 and assign2 % columns. 
  3. Format the average, maximum and minimum to all have 1 decimal place. 
  4. Use a formula to calculate the numbers in the average% column, this is the assign1% added to the assign2% divided by 2 – beware of BODMAS! 
  5. Format the average5 column to display 1 decimal place. 
  6. Align titles and numbers to make your spreadsheet look professional. 
  7. Format the table with a border, title shading, and centered horizontally and vertically on the page. 
  8. Add your name using a header and the date and time as a footer to the spreadsheet, save and print preview.


Propellerads