W1-2-60-1-6
JOMO KENYATTA UNIVERSITY
OF
AGRICULTURE AND TECHNOLOGY
University Examinations 2012/2013
STAGE I EXAMINATION FOR THE DIPLOMA IN INFORMATION TECHNOLOGY
DIT 0103: SPREADSHEETS
DATE: AUGUST, 2012 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and Any Other TWO Questions.
Question One – 40 marks
a) Define the following terms:
i. Legend [2 marks]
ii. Template [2 marks]
iii. Function [2 marks]
b) Different between the two referencing styles used in spreadsheets by using examples.
[6 marks]
c) State the steps of creating a named range. [3 marks]
d) State five application areas of spreadsheets. [5 marks]
e) Describe the three components of a spreadsheet. [6 marks]
f) State any five advantages of using electronic spreadsheets. [5 marks]
g) Differentiate between copying and moving data in a worksheet. [4 marks]
h) State aid explain briefly the four datatypes used in spreadsheets. [5 marks]
Question Two – 20 marks
a) A worksheet contains the data shown below:
| Cell | A1 | A2 | A3 | C1 | C2 | C3 | G1 | G2 |
| Entry | 5 | 7 | 10 | 10 | 15 | 15 | =sum if(A1:C3”<=10”) |
State the value displayed in G1. [3 marks]
ii. Write the formular or function that can be used to display the highest vlue/entry from the range in cell G2.
iii. Using the nested if control structure award a grade to the sum at cell G1 whereby:
70 – 100 – A
50 – 70 – B
<49 – C [5 marks]
| A | B | C | D | E | |
| 1 | Name | Score | Award | ||
| 2 | James | 50 | |||
| 3 | Mary | 33 | |||
| 4 | Ann | 62 | |||
| 5 | |||||
| 6 | Rate | 30 | |||
| 7 | |||||
| 8 |
Differentiate between absolute referencing and relatives referencing style. [4 marks]
Question Three
a) A worksheet contains the data as shown below:
| A | B | C | D | E | F | G | ||
| 1 | 8 | 20 | ||||||
| 2 | 11 | 45 | ||||||
| 3 | 14 | 52 | ||||||
| 4 | ||||||||
| 5 |
The formula = COUNTIF (C1: C3, “20”) was entered at G1. Write down the value that was displayed. [2 marks]
ii. 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]
iii. 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. [2 marks]
b) Use the following information to answer the questions below:
Total Remark
350 – 500 Distinction
300 – 349 Credit
200 – 299 Pass
0 – 199 Fail
Assuming the total mark is in cell D4. Write a nested if statement that will allocate the
remarks above. [5 marks]
c) Differentiate between copying and moving data in a worksheet. [4 marks]
d) State the four datatypes accepted to spreadsheets and briefly explain each. [4 marks]
