MUKAA BOYS’ HIGH SCHOOL

COMPUTER STUDIES, FORM TWO 2024

DECEMBER HOLIDAY ASSIGNMENT

NAME ……………………….……………………………… ADM / NO …….........

CLASS …………………………………………….......…… DATE ………….........…

SPREADSHEETS – MS EXCEL

1. (a) What is Electronic spreadsheet software? (2 marks)

(b). Explain the use of electronic spreadsheet software in business organizations.
(4 marks)
2. Differentiate between the traditional analysis ledger sheet and an electronic spreadsheet.
(5 marks)
3. (a) Describe the three components of a spreadsheet. (3 marks)
(b) Apart from Microsoft Excel, give any two other application programs classified as spreadsheets.
(2 marks)
4. Explain five application areas where spreadsheet software can be used. (5 marks)
5. Describe any five features (advantages) of electronic spreadsheet software. 5 marks)
6. Explain the following terms as used in spreadsheets. (4 marks)
(a) Columns.
(b) Rows
(c) Cell.
(d) Chart.
(e) Automatic recalculation.
7. Explain the concept of ‘What if’ analysis. (2 marks)
8. (a) Explain the term ‘Range’. (1 mark)
(b) State two actions that can be performed on ranges in Microsoft Excel. (2 marks)
9. State any four data types used in a spreadsheet. (2 marks)
10. List four formatting features provided by Microsoft Excel. (4 marks)
11. Define the following terms as used in computer environment. (3 marks)
(i). Operator
(ii). Operands
(iii). Operation
12. (a) Define the following terms as used in spreadsheets: (6 marks)
(i) values
(ii) formula
 (iii) Function
(iv) Labels
(b). List four mathematical functions provided by Microsoft Excel. (4 marks)

13. (a) The following is a simple payroll:

A B C D E F G H I
1 Name Hours Hourly Basic Gross Tax NSSF Allowance Net
Worked Rate Pay Pay Deductions Contributions Pay
2 John 8 200
3 Peter 12 450
4 Sam 22 300
5 Njogu 30 286
6 Mary 16 220
7 Sally 45 468
8 Jane 15 150
9 Tina 3 280
Write formulae using cell names for the following expressions. State where the formula is placed:
(10
marks)
(i) Basic pay = Hours worked x Hourly rate.
(ii) Allowances are allocated at 10% of the Basic pay.
(iii) Gross pay = Basic pay + Allowances.
(iv) Tax deduction is calculated at 20% of the Gross pay.
(v) Net pay = Gross pay – Tax deductions.
(b). List four types of information that can be entered into a spreadsheet cell. (4 marks)
14. (a) What is a cell reference? (1 mark)
(b) Mention four examples of cell reference (2 marks)
(c) Distinguish between Absolute cell reference and Relative cell reference. (2 marks)
(d) For each of the following, state the type of cell reference. (4 marks)
i) A5
ii) $F$5
iii) H$21
iv) $D7
15. Compute: (2 marks)
(i). 37 MOD 5
(ii). 37 DIV 5
16. (a) A formula to add the contents of B5 and C4 was entered in cell F5. What will it become when it
is copied to cell H8? (1 mark)
(b) Explain the reason for your answer. (2 marks)
17. (a) Write the formulae =F10 + G20 as absolute. (1 mark)
(b) The formulae =A1+C2 is initially typed in cells D1. What will it be when copied to cell E1?
(1
mark)
(c) What is the equivalent R1C1 reference for G20? (1 mark)

18. Give at least five categories of functions that are available in Microsoft Excel. (5 marks)
19. What is the role of the following functions as used in a spreadsheet program? (5 marks)
(a) Product
(b) SQRT
(c) Average
(d) Max
(e) IF
(f) COUNTIF
(g) SUMIF
20. A worksheet contains the data shown below:
Cell A1 A2 A3 C1 C2 C3 G1
Entry 5 7 10 10 15 15 =SUMIF (C1:C3, “<> 10”, A1:A3)

State the value displayed in G1. (2 marks)

21. Explain why a value such as 611233444555 may be displayed as ######### when typed on a
spreadsheet. (2 marks)
22. (a). Assuming that the formula ‘= A5 * $B2’ is in cell C10 of a spreadsheet. Show how it will
appear after copying it to cell H12. (1 mark)
(b). Explain how you would select non-contiguous cells in spreadsheet. (2 marks)
23. A worksheet contains the data as shown below.
A B C D E F G
1 5 10
2 7 15
3 10 17
4
5
6
7
8
9
1
0
(a) The formula =COUNTIF (C1:C3, “> 10”) was entered at G1. Write down the value that was
displayed. (2 marks)
(b) Write down the formula that would be entered at cell B7 to sum the values in column A whose
values are greater or equal to 5. (3 marks)
 (c) The formula = $C2 + C$3 is entered in cell C5 and then copied to D10. Write down the formula
as it appears in the destination cell. (3 marks)
24. (a) What is a Chart wizard in spreadsheets? (1 mark)
(b) Give two examples of charts that you know. (2 marks)
(c). Outline the steps required when creating a simple chart. (6 marks)
25. Andrew, Jane, David and Zablon had Tea, Sausages and Bananas for breakfast. They took one
sausage, two sausages, three sausages and one sausage respectively. In addition, they each took a
cup of tea and two bananas. Tea, sausages and bananas cost Ksh. 10, 15, and 5 respectively.

(a) By naming columns A, B, C, ………and rows 1, 2, 3……….Construct a worksheet showing the

above information. (7 Marks)
(b) State the expression you would use to obtain:
i) Total expenditure by David. (4 marks)
ii) Total number of sausages taken. (2 marks)
iii) The cost of the cheapest item. (2 marks)
26. The following diagram is a Microsoft Excel worksheet containing the scores of Form 1 students of
Excellent High school.
A B C D E F G
1 STUDENT NAME ENG KISW MATH SCI
2 Ali Shah 75 65 80 78
3 Arthur Kamau 80 78 58 72
4 Maalim Ahmed 75 78 64 80
5 Harry Mutua 65 84 78 81
6 Martin Mulama 90 81 57 74
7 Keben Korir 73 65 85 78
Write Microsoft Excel formula to calculate:
(a) Total score for each student. (1 mark)
(b) Highest score per subject. (1 mark)
(c) Mean score per subject. (1 mark)
(d) Best overall student. (1 mark)
27. What is a cell reference error as used in spreadsheets? (1 mark)
28. A worksheet contains the data shown below:
A B C D
1 Jane
2 Kim
3 June
4 Jack
5 Jane
(a). The formula =IF(A1:A5 = “Jane”, 1, 0) is entered in cell B1
(i). State the value displayed (2 marks)
(ii). If the formula in B1 is copied and pasted to cells B2, B3, B4 and B5 respectively, fill in
what is displayed in each cell. (2 marks)
(b). Under what two conditions does a worksheet display # # # # # # (2 marks)
 (c). A spreadsheet application can be used in analysis of trends of performance. List any three
types of charts you can make. (3 marks)
29. Consider the entries made in the cells below:
Cell B2 B3 C10 C11 C13
entry 200 100 B2 B3 =C10 + C11

State the value displayed in cell C13. (1 mark)

30. A student presented a budget in the form of a worksheet as follows.
A B C
1 Item Amount
2 Fare 200
3 Stationery 50
4 Bread 300
5 Miscellaneous 150
6 Total

The student intends to have spent half the amount by mid-term.

(a). Given that the value 0.5 is typed in cell B9, write the shortest formula that would be typed in
cell C2 and then copied down the column to obtain half the values in column B.
(1
mark)
(b). Write two different formulae that can be typed to obtain the total in cell B6 and then copied to
cell C6. (2 marks)
31. The cells K3 to K10 of a worksheet contain remarks on students’ performance such as Very good,
Good, Fair and Fail depending on the average mark. Write a formula that can be used to count all
students who have the remark “Very good”. (3 marks)
32. The following information shows the income and expenditure for “Bebayote” matatu for five days.
The income from Monday to Friday was Kshs. 4,000, 9,000, 10,000, 15,000, and 12,000 respectively
while the expenditure for the same period was Kshs. 2,000, 3,000, 7,000, 5,000, and 6,000
respectively.
(i) Draw a spreadsheet that would contain the information. Indicate the rows as 1, 2, 3 …. and the
columns as A, B, C ….. (4 marks)
(ii) State the expression that would be used to obtain:
I Monday’s profit (2 marks)
II total income (2 marks)
III highest expenditure. (2 marks)
33. (a) Distinguish between the following sets of terms as used in spreadsheets.
(i) Worksheet and workbook. (2 marks)
(ii) Filtering and sorting. (2 marks)
(b) State one way in which a user may reverse the last action taken in a spreadsheet package.
(1
mark)
 (c) The following is a sample of a payroll. The worksheet row and column headings are marked 1, 2,
3 … and A, B, C … respectively.

A B C D E F G H
1 NAME HOURS PAY PER BASIC ALLOWANCES GROSS TAX NET
WORKED HOUR PAY PAY DEDUCTIONS PA
Y
2 KORIR 12 1500
3 ATIENO 28 650
4 MUTISO 26 450
5 ASHA 30 900
6 MAINA 18 350
7 WANJIKU 22.5 500
8 WANYAM 24.5 250
A
9 OLESANE 17 180
1 MOSETI 33 700
0
TOTALS
Use the following expressions to answer the questions that follow:
 Basic pay = Hours worked x pay per hour
 Allowances are allocated at 10% of basic pay
 Gross pay = Basic pay + allowances
 Tax deductions are calculated at 20% of gross pay
 Net pay = Gross pay – tax deductions

Write formulae using cell references for the following cells:

(i) D2 (1 mark)
(ii) E4 (1 mark)
(iii) F10 (1 mark)
(iv) G7 (1 mark)
(v) H5 (1 mark)