SPREADSHEETS – MS EXCEL

1. (a) What is Electronic spreadsheet software?

2. (b). Explain the use of electronic spreadsheet software in business organizations.

3. Differentiate between the traditional analysis ledger sheet and an electronic spreadsheet.

4. (a) Describe the three components of a spreadsheet.

(b) Apart from Microsoft Excel, give any two other application programs classified as spreadsheets.
5. Explain five application areas where spreadsheet software can be used. (5 marks)
6. Describe any five features (advantages) of electronic spreadsheet software. 5 marks)
7. Explain the following terms as used in spreadsheets. (4 marks)
(a) Columns. Rows, Cell, Chart, Automatic recalculation.
8. Explain the concept of ‘What if’ analysis. (2 marks)
9. (a) Explain the term ‘Range’. (1 mark)
(b) State two actions that can be performed on ranges in Microsoft Excel. (2 marks)
10. State any four data types used in a spreadsheet. (2 marks)
11. List four formatting features provided by Microsoft Excel. (4 marks)
12. Define the following terms as used in computer environment. (3 marks)
(i). Operator, Operands, Operation
13. (a) Define the following terms as used in spreadsheets: (6 marks)
(i) Values, formula, Function, Labels
(b). List four mathematical functions provided by Microsoft Excel. (4 marks)
14. (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:
(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)
15. (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 , $F$5, H$21, $D7
16. Compute: (2 marks)
(i). 37 MOD 5, 37 DIV 5
17. (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?
(b) Explain the reason for your answer. (2 marks)
18. (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?

(c) What is the equivalent R1C1 reference for G20? (1 mark)

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,SQRT,Average,Max,IF,COUNTIF,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

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
(a) 0 The formula =COUNTIF (C1:C3, “>
10”) was entered at G1. Write down the value that was displayed.
(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.
(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.
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.
(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.
(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.
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 Miscellaneou 150
s
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.
(b). Write two different formulae that can be typed to obtain the total in cell B6 and then copied to cell C6.
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”.
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 …..
(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.

(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 BASI ALLOWANCES GROSS TAX NET
WORKED HOUR C PAY DEDUCTIONS PA
PAY 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) DE4 F10 G7 H5

INTERNET & E-MAIL

1. Explain the following: (3 marks)

(a) Internet., Intranet. File Server.
2. List any three major services provided on the Internet. (3 marks)
3. Name four facilities that are needed to connect to the Internet. (4 marks)
 4. Your manager wishes to be connected to the Internet. He already has a powerful Personal Computer (PC), a
Printer, and access to a Telephone line. However, he understands that he will need a Modem.
Required:
(a) State why a modem is required to connect him to the Internet. (2 marks)
(b) Suggest any four application areas in which you would expect a supermarket retail manager to use the
Internet.
5. (a) What is a Website? (2 marks)
(b) Give the advantages and disadvantages of a Website. (4 marks)
6. (a) What is meant by the term E-learning? (1 mark)
(b) A school intends to set-up an e-learning system. List three problems that are likely to be encountered.

7. (a) What are network Protocols?

(b) Write the following in full:
TCP/IP, HTML, HTTP, FTP
8. (a) Explain the meaning of the following concepts as used in Internet: (6 marks)
i) Internet service provider (ISP), Web pages, Internet telephony, Browser software, Hyperlink
(b) Name three examples of Internet Service Providers (ISP) in Kenya. (3 marks)
9. Give two common examples of web browsing software. (1 mark)
10. Briefly describe four advantages of using Internet to disseminate information compared to other conventional
methods.
11. (a) Identify the parts of the following e-mail address labelled A, B, C, and D. (4 marks)

Iat@[Link]

A B C D
(b) Mention two examples of e-mail software. (2 marks)
12. A school has its e-mail address as mwangaza@[Link]. Briefly explain this address code.

13. State two benefits of saving information from the Internet to your hard disk. (2 marks)
14. Explain the following internet address [Link] in reference to the structure of a URL
15. Identify institutions whose e-mail addresses end with the following extensions: (6 marks)
i) .org. edu .com .net .mil .gov
16. (a) Discuss four advantages and two disadvantages that electronic mails have over regular mails
17. Give three differences between Post-office mail and Electronic mail (E-mail).

18. (a) What is a Search engine?

(b) Give four examples of search engines you know. (2 marks)
(c) State two ways that search engines use to locate Web pages. (2 marks)
19. List two advantages of using Hyperlinks when browsing the Internet. (2 marks)
20. Differentiate between a www server and a Host computer. (2 marks)
21. The Internet can be used to source information about emerging issues that may not be available in print form.
Give two advantages and two disadvantages of information obtained from the Internet.
(4 marks)

DATA SECURITY & CONTROL

1. (a) Differentiate between Data Security and Data Integrity. (2 marks)

(b) Give the three types of data that should be protected in a computer. (3 marks)
2. State any three threats to data and information. (3 marks)
3. State five possible ways of preventing data loss from a computer. (5 marks)
4. (a) Define the term Computer crime. (2 marks)
 (b) Explain the meaning of each of the following with reference to computer crimes.
i) Tapping, Piracy., Trespass, Industrial espionage, Data alteration Fraud Firewalls
5. Give two reasons that may lead to computer fraud. (2 marks)
6. Outline four ways of preventing piracy with regard to data and information. (4 marks)
7. (a) Differentiate between Hacking and Cracking with reference to computer crimes.
(b) Describe the following terms with respect to computer security: (6 marks)
Audit trail. Data Encryption. Log files. Firewalls. Physical security
Logic bombs.
8. (a) What is a Computer virus? (2 marks)
(b) Outline four symptoms of a virus infection in a computer system. (4 marks)
(b) State two damages which a computer virus may cause to a computer. (2 marks)
(c) Explain three control measures you would take to protect your computers from virus attacks.
9. List three functions of an antivirus software. (3 marks)
10. Computer systems need maximum security to prevent an unauthorized access. State six precautions that you
would expect an organization to take to prevent illegal access to its computer-based systems.
11. (i) Explain what is meant by the term “computer security” (2 marks)
(ii) State two environmental factors that can affect operations of a computer. (2 marks)
(iii) State two control techniques or measures that can be implemented to prevent the effect in (i) above.

12. Explain why the following controls should be implemented for computer based systems.
i) Backups Air conditioning Uninterruptible power supply (UPS) Segregation of duties
Passwords
13. Give four rules that must be observed in order to keep within the law when working with data and information
14. rks) (a) Define the term Computer ethics. (1 marks)
(b) Give two examples to show how a person who has committed a computer crime can help to improve a
computer system.
FORM THREE - DATA REPRESENTATION IN COMPUTERS
Data in a computer is represented in one major form. Define the term ‘Data representation’ in a computer.
1. (a) Differentiate between Analogue data and Digital data. (2 marks)
(b) Draw a sketch of:
(i). Analogue data signal. Digital data signal.
2. Give two reasons for the popularity of binary number representation.
3. Explain the role of a Modem in communication. (2 marks)
4. Distinguish between the following terms as used in data representation in computers:
(i). A Byte and a Nibble. (2 marks)
(ii). Word and Word length. (2 marks)
5. Arrange the following data units in ascending order of size.
BYTE, FILE, BIT, NIBBLE. (2 marks)
6. Write out what A, B, C and D represent in the table below. (4 marks)
Number System Values
A 0, 1
B 0, 1, 2, 3, 4, 5, 6, 7
C 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
D 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F

7. Perform the following computer arithmetic. In each case, show how you arrive at your answer.

(a) Convert the following Decimal numbers to their Binary equivalent.

i) 11
ii) 001
iii) 457
 (b) Convert the following Octal numbers to their Binary equivalent.
i) 77 (2 marks)
ii) 0000001 (2 marks)
(c) Use Binary addition to solve the following decimal summations.
i) 410 + 310 (2 marks)
ii) 1310 + 210 (2 marks)
(d) Convert the following Hexadecimal numbers to their Binary equivalent.
i) C3 (3 marks)
ii) 13 (3 marks)
(e) Convert the following Binary numbers to their Hexadecimal equivalent.
i) 110111.11 (2 marks)
ii) 1.1110101 (2 marks)
iii) 110000111111111111 (2 marks)
8. (a) State one use of hexadecimal notation in a computer. (1 mark)
(b) Convert 7678 to hexadecimal. (2 marks)
9. Use One’s compliment to solve the following sums:
i) 9–6 (3 marks)
ii) 17 – 15 (3 marks)
iii) 1110 – 1011 (2 marks)
iv) 111010 – 110011 (2 marks)
10. Perform the following conversions:
i) 20.216 to decimal. (3 marks)
ii) 111012 to Decimal. (3 marks)
11. (a) Perform the following Binary arithmetic: 75 + 45 (2 marks)
(b). Use Two’s compliment to perform the following Binary subtraction:
i) 10111 – 10001 (2 marks)
ii) 11000 – 10011 (2 marks)
12. Use Two’s compliment to solve the following SUMS (the numbers are in decimal notation)

i) 23 – 20 (3 marks)
ii) 17 – 14 (3 marks)
13. Perform the following binary arithmetic:
(i). 11100111 + 00101110 (1 mark)
(ii). 1000 – 101 (using 2’s complement) (2 marks)
14. Convert the decimal number 4 ¾ into binary form. (4 marks)
15. Convert the binary coded decimal number given into its hexadecimal equivalent.
100010012 (show your work clearly) (2 marks)

16. Work out the 8-bit binary two’s complement of the number -210 (3 marks)
17. Convert the hexadecimal number FC1 to its binary equivalent. (6 marks)
18. Convert 7AE16 to a decimal number. (2 marks)
19. State three methods of representing data in binary number system. (3 marks)
20. (a) Explain Binary Coded Decimal code of data representation. (1 mark)
(b) Write the number 45110 in BCD notation. (1 mark)
21. (a) Subtract 01112 from 10012 (1 mark)
(b) Using two’s complement, subtract 7 from 4 and give the answer in decimal notation.
(4 marks)
(c) Convert:
(i) 91B16 to octal (3 marks)
(ii) 3768 to hexadecimal (3 marks)
(iii) 9.62510 to binary (4 marks)