S4 CIT
Common Application Software
Excel Mock Test
Section A: MC Exercise
1.Which of the following is not a function of a spreadsheet?
A.recalculationB.relative addressing
C.query using SQL D.charting
1994 CE #15
2.The manager of a company wants to analyze a profit-and-loss account. Which of the following software should he use?
A.a Chinese text processorB.a word processor
C.a spreadsheet programD.a graphics package
E.a communication package
1995 CE#14
3.(1)calculating monthly instalments(分期付款) for different interest rates
(2)plotting a histogram of examination mark distribution
(3)designing the shape of a car
Which of the above tasks can be carried out by a spreadsheet program?
A.(1) onlyB.(3) only
C.(1) and (2) onlyD.(2) and (3) only
E.(1), (2) and (3)
E.selling price
2001 CE#21
4. / Consider the following spreadsheet:A / B / C
1 / 10 / 15 / 20
2 / 35 / 20 / 5
Cell A3 contains the formula =#Al+A#2.
A # sign before any column letters or row numbers represents absolute addressing. Without the # sign, relative addressing is used. If the formula stored in cell A3 is copied to cell B3, what will be the value shown in cell B3?
A.0B.25
C.30D.45
2003 IT #34
5.
A / B / C / D1 / Name / Chinese / English / Total
2 / Student X / 66 / 72 / 138
3 / Student Y / 58 / 69 / 127
4 / Student Z / 89 / 45 / 134
5 / Average / 71 / 62
The above spreadsheet gives the marks of three students in Chinese and English tests. The total mark of each student is calculated in column D while the average mark of each subject is calculated in row 5. Which cells will have their values changed if the data of cell B3 is modified to “75”?
A.cells B5 and C5B..cells B5 and D3
C.cells C3 and C5D.cells C5 and D3
6.The address of a cell in a spreadsheet is E31. The content of the cell is =$A$10+10. Which of the following statements is/are correct?
(1)The cell is located in column E and row 31.
(2)The address in the formula is a relative address.
(3)When the cell is copied and pasted to other location, the new result is the same as that in E31.
A.(1) onlyB.(2) only
C.(1) and (3) onlyD.(1), (2) and (3)
7.The formula =C3*2 is entered in cell C10. When the formula is copied and pasted to D11, the formula in D11 will be
A.=C3*2B.=C4*2
C.=D3*2 D.=D4*2
8.
A / B / C / D1 / 5 / 6 / 3
2 / 12 / 8 / 2
3 / 4 / 10 / 7
4
The formula=SUM($A1:$A3) in A4 is copied and pasted to B4. What is the result appeared in B4?
A.7B.8
C.21D.24
9.Relative address will change when
A.the data in a spreadsheet is changed.
B.a formula is copied and pasted to other cells.
C.the address does not have any data.
D.the data is numeric.
2002 CE #14
10. / Consider the following worksheet in a spreadsheet package:A / B
1 / Amount / 10000
2 / Interest Rate / 3%
3
4 / Year / Interest
5 / 1 / 300
6 / 2 / 600
Cell B6 stores the product of B1, B2 and A6.If the formula stored in cell B6 is produced by copying the formula stored in cell B5, what should be the formula in cell B5?(A $ sign before any column letters or row numbers represents absolute addressing. Without the $ sign, relative addressing is used.)
A.B1*B2*A$5B.B$1*B$2*A5
C.$B1*$B2*A5D.B1*B2*$A5
Section B: Conventional Questions
2001 CE#1
1.
(a)The values in cells E2, E3, E4 and E5 are calculated by the following mathematical expression:
Test 1 x Weight of Test 1 + Test 2 x Weight of Test 2 + Exam x Weight of Exam
These values represent the assessment totals of students.
(i)Write the formula that should be stored in cell E2.
______
(ii)A student find the correct value in cell E2. When the formula stored in cell E2 is copied to cells E3, E4 and E5, the figures showing the totals in cells E3, E4 and E5 are wrong. Suggest ONE reason for the error.
______
______
(4 marks)
(b)Cell E6 is used to show the average of the 4 assessment totals. Write the formula that should be stored in cell E6. (2 marks)
______
(c)Give ONE advantage of using an electronic spreadsheet, instead of a calculator, to find the assessment results of students. (2 marks)
______
3.Given that
annual interest = principal x annual interest rate
Peter uses a spreadsheet software package to calculate interest earned annually. He has typed the data and formulae in a worksheet as follows: (Italic figures are generated by formulae)
A / B / C1 / Annual interest rate: / 0.03
2 / Principal: / 1,000
3 / Year / Principal invested at the beginning of the year / Interest earned over the year
4 / 2003 / 1,000 / 30
5 / 2004 / 1,030 / 1,030,000
6 / 2005
7 / Total interest earned:
The formulae in C4 is =B4*C1.
(a)Using cell references, write down the formula in B5.
______
(b)(i)Peter copies the formula in C4 into C5. What is the formula in C5?
______
(ii)Peter finds that the displayed result in C5 is wrong. The correct value should be 30.9. Rewrite the formula in C4 so that copying the formula to C5 and C6 will not cause any error.
______
(iii)Instead of using cell references, one possible formula in C4 is =1000*0.03. Give one advantage of using cell references over actual numeric values.
______
______
(c)Assume that Peter has worked out all correct formulae to calculate the interest earned over each year.
(i)Using a spreadsheet built-in function, write down the formula in C7 to calculate the total interest earned over the three years.
______
(ii)Peter wants to create a bar chart to show the annual interest earned over the three years. State the cell ranges to be used for the chart.
______
Endof Common Application Software Excel Mock Test
Ans:
1. / C / 2. / C / 3. / C / 4. / C / 5. / B6. / C / 7. / D / 8. / C / 9. / B / 10. / B
1.(a)(i)= B2 * $B$8 + C2 * $B$9 + D2 * $B$10
(ii)The formula may have used absolute addressing for all of its cell reference.
(b)= AVERAGE(E2:E5)OR = (E2 + E3 + E4 + E5) / 4OR = SUM(E2:E5) / 4
(c)When an electronic spreadsheet is used, the assessment results of students can be updated automatically when there are changes in the marks of students. If a calculator is used, all results will have to be re-calculated manually.
2.(a)= B4 + C4
(b)(i)= B5 * C2
(ii)= B4 * C$1
(iii)Using cell references allows the related data to be changed automatically by recalculation by the spreadsheet software, whenever there is a change in the related cells (ie: interest rates)
(c)(i)= SUM(C4 : C6)(OR= C4 + C5 + C6)
(ii)A4 : A6, C4 : C6
______P.1
S4/ICT/Common Application Software/Excel Mock-Test