Overview

Sheet 1: data

CLSNO	Sex	CHI	ENG	MAT	SCI	GEO	HIS	CHS	CL	PTH	PE	MUS	RSC	Total	Rank
1	M	66	76	76	80	67	69	83	84	86	58	92	77	914	2
2	F	73	65	69	73	58	58	44	90	79	71	56	42	778	9
3	F	51	68	73	70	75	77	92	50	75	0	73	77	781	8
4	M	74	85	73	60	75	71	85	72	78	55	94	90	912	3
5	F	60	80	76	68	38	58	81	69	80	0	71	72	753	10
6	F	72	86	72	78	67	70	65	83	89	60	87	83	912	3
7	F	49	75	70	68	58	69	88	84	79	64	69	78	851	6
8	M	61	88	80	68	50	58	95	87	87	71	90	90	925	1
9	M	70	70	78	63	67	77	74	84	86	66	86	80	901	5
10	M	65	73	70	55	67	53	83	84	81	52	72	62	817	7

		CHI	ENG	MAT	SCI	GEO	HIS	CHS	CL	PTH	PE	MUS	RSC
Average mark		64	77	74	68	62	66	79	79	82	50	79	75
Performance		Fair	Good	Fair	Fair	Fair	Fair	Good	Good	Good	Fair	Good	Good

Sheet 2: question

Section A - Use formulae and functions to complete the following tasks.

1. Find the average mark of each subject in cells C14 to N14. (1 mark)

2. Without changing the formulae for the average marks, display the values to the nearest integer. (1 mark)

3. Put down the word 'Good' if the subject average mark is higher than 75, otherwise put down 'Fair'. Put the formulae in C15 to N15. (1 mark)

4. Find the totals of the marks of all students in O2 to O11. (1 mark)

5. Rank the students by their total marks in column P. Student with the highest mark should rank 1. (1 mark)

6. Sort the records primarily on Science and secondary on Mathematics. Both are in descending order. (1 mark)

7. Create a new worksheet named 'new'. (1 mark)

8. Find the students who are in 'Top 5' of both Chinese and Mathematics. Copy the answer to 'new'. (1 mark)

9. Show all records first. Set a validation rule that the range C2 to N11 should contain integers only ranging from 0 to 100. Write an appropriate error message for it. (1 mark)

10. Draw a chart, with suitable labels and title, showing the average marks of each subject on the same worksheet.(2 marks)

11. Adjust the page setup in order to print this page in 1 A4 paper with your name in the header. (1 mark)

Hosted by www.Geocities.ws