Download Exercise excel for beginners and more Exercises Computer science in PDF only on Docsity!
Exercise 1.
The following students sat their exams in Physics (P), Chemistry (C), math (M) and Comp Studies (CS). They obtained the following marks. Yasin scored 52 in P, 86 in C, 46 in M and 65 in CS. Sophia scored 41 in P, 65 in C, 50 in M and 70 in CS. Ahmed scored 22 in P, 60 in C 36 in M and 45 in CS. Baker scored 42 in P, 66 in C, 51 in M and 55 in CS. Dan scored 53 in P, 44 in C, 50 in M and 32 in CS. Peter scored 62 in P, 69 in C, 52 in M and 59 in CS. Rose scored 15 in P, 25 in C, 32 in M and 10 in CS. Andy scored 80 in P, 70 in C, 60 in M and 50 in CS. Musa scored 45 in P, 53 in C, 66 in M and 52 in CS. Sam scored 23 in P, 34 in C, 45 in M and 56 in CS. (a) Enter all the data in a spreadsheet with all names in column A and save it as students. (b) Copy the work to sheet two. (c) Sort the names in ascending order. (d) Add visible borders to your work. (e) Center all the marks. (f) Add another student between Sophia and Ahmed with the following scores in the four subjects respectively 45, 92, 67 and 51 (g) Use a formula to calculate the Total and Average marks for each student. (h) Save changes you have made in your work.
Exercise 2. A school Canteen has the data below as the sales records for the months of May, June, July and August. May Sales: Cakes - 50 packets, Biscuits 2500 packets, Chocolates – 250 packets, Sweets – 4500 packets. June Sales: Cakes - 78 packets, Biscuits 2300 packets, Chocolates – 250 packets, Sweets – 2500 packets. July Sales: Cakes - 76 packets, Biscuits 2600 packets, Chocolates – 510 packets, Sweets – 3000 packets. August Sales: Cakes - 60 packets, Biscuits 2000 packets, Chocolates – 500 packets, Sweets – 2800 packets.
(i) Enter the above given data in a suitable Spreadsheet program. Use months as columns. ( 04 marks) (ii) Using formulas find the:
- Total number of packets for each item sold in four months. Label this column “ Totals” ( 04 marks)
- Average unit sales per month per item. Label this column “Average” ( 04 marks) (iii) Draw a pie-chart showing the monthly chocolate sales in percentages. include your personal number below the pie-chart. ( 05 marks) (iv) Print your table and Pie-chart (02 marks) (v) Save your as “Sales” (01 mark)
Exercise 2.
Bulyanyi Muslim Secondary School produced the mark sheet for Class A using a spreadsheet application. Enter the data below and save it as “ Bulanyi” (06 marks)
Bulanyi Muslim Secondary School Mark Sheet for Class A
Personal No.
Name Physics Chemistry Biology Mathematics Total Average Rank
1001 Kyajjakuzim ba
65 78 56 45
1002 Nambuusi 53 85 88 78 1003 Walusimbi 45 96 76 98 1004 Kagimu 78 76 74 65 1005 Nyangoma 23 59 65 36 1006 Kiwalabye 78 68 67 52 1007 Jjuuko 65 53 63 32 1008 Niwamanya 55 65 65 45 1009 Rutalihamwe 48 54 75 58 1010 Ssempangi 88 64 45 79 1011 Kisekka 46 58 61 45 1012 Katende 54 74 52 56
Max Min
(i) Using an appropriate formulae calculate the total (01mark) (ii) Using appropriate formulae calculate the total (01mark) (iii) Using appropriate function to rank the students basing on their Average marks (01mark) (iv) Find the maximum mark for Physics, Chemistry, Biology and Mathematics (02 marks) (v) Find the minimum mark for Physics, Chemistry, Biology and Mathematics (02 marks) (vi) Using conditional formatting, shade the cells for which the average is greater than 60 with a red colour. (02 marks) (vii) Arrange the names in order of Z-A. (01mark) (viii) Using Name and Total Score, Create a 3-D Clustered Column Chart with visual effect (02 marks)
(ix) Print your work. (01 mark)
Exercise 4.
The table shows end of year 2002 promoted results of P.6 pupils from Hoima primary School. Enter the data in a spreadsheet and save the file as NJERU. (03 marks)
INDEX No.
NAME MTC ENG SST SCI TOTAL AVE POS COMMENT
U56/01 Mudde Mike U56/02 Ohoko John U56/03 Namire Flavia U56/04 Majingo Fred U56/05 Tusabe Fahad U56/06 Lulenzi Joseph U56/07 Apio Oliver U56/08 Kisakye Mercy U56/09 Tawoda Isa U56/010 Gumire Deo TOTAL MARKS
(i) Use a suitable function to calculate the total marks scored by each pupil (02 marks) (ii) Use a suitable function to compute the average marks scored by each pupil (02 marks) (iii) Use a suitable function to generate a position for each pupil (02 marks) (iv) If students with an average of 65 and above are to be PROMOTED to P.7, pupils with an average between 50 and 65 are to REPEAT P.6 while students with an average of 50 and below should TRY ANOTHER SCHOOL. Use a suitable function to generate the above uppercase comments accordingly. (02 marks)
(v) Sort the data in ascending order by name. (02 marks) (vi) Sum up the total marks scored by all pupils in MTC, ENG, SST and SCI. (vii) Use the sums obtained in (Vi) above to create a Pie-chart showing the subject performance. (02 marks) (viii) Add your name and index number as a footer on your worksheet. (01 mark) (ix) Print your work. (01 mark)
Exercise 5. (a) Open an appropriate spreadsheet software of your choice and enter the data shown below in the worksheet: Save your worksheet as S.4 mark sheet. (03 marks)
S/N NAME SEX HIST CRE GEO COMP 1 F 90 54 78 50 2 M 79 53 70 89 3 F 87 65 69 65 4 F 76 57 82 45 5 F 45 66 55 76 6 M 76 44 76 87
(b) In the TOT column, calculate the TOTAL MARKS of each student (01mark) (c) In the AVE column, calculate the AVERAGE MARK for each student (01mark) (d) In the cell B10, type the label “Highest Score” and calculate the highest scores per subject, Total Mark and Average mark. (02marks) (e) In cell B11, type the label “Lowest and calculate the lowest scores per subject, Total Mark and Average mark. (02marks) (f) Create a suitable title for the table in the spreadsheet. (01mark) (g) Introduce another column titled PASS/FAIL (01mark) (h) Given that the Pass mark/cut off point for this class is 62.45%, Use the IF function to assign all student Below 62.45%, FAIL, those between 62.45% and 65%, PROBATION and those above 65%, PASS. (03marks) (i) Introduce a GRADE column after every subject and use the VLOOKUP function to assign a correct grade for every subject. (04marks) Save your work in your own folder and produce a printout. (02marks)
Exercise 6.
The table below shows examination results of Senior Six students of Mugo Secondary School. Study it and answer questions that follow.
A B C D E F G H I J 1 MUGO SECONDARY SCHOOL SENIOR SIX SCIENCE CLASS, 2013 2 SUBJECTS 3 FIRS T NAM E
LAS
T
NAM
E
BIOLO
GY
CHMIST
RY
MAT
H
PHYSI
CS
IC
T
AVERA
GE
GRAD
E 4 5 6 7 8 9 1 0 1 1 1 2 1 3 1 4 1 5 1
