Answers
Section 7: Spreadsheets
Check your progress 1
1. Explain the difference between a spreadsheet and a worksheet.
A. A spreadsheet file contains at least one or more worksheets. A worksheet
is a single page in a spreadsheet file.
2. List the cells in the range A2:B3.
A. A2, A3, B2, B3
3. In which row and which column does the cell P64 lie?
A. Row 64, column P
4. What character must always be used when a formula is entered into a cell?
A. = (equals sign)
5. Suppose that you want to enter the value 2010 as a label in a cell. How would
you do this to avoid it being used in a calculation?
a. Insert a single quotation in front of it, e.g. ‘2010.
1
Check your progress 2
Consider the following worksheet that is used to compute the monthly payroll of
an organisation.
A B C D E F G
1 Employee Salary Allowanc Gross Pay Medical Tax Net Pay
e
2 Lawrence 980000 40000
3 Karen 45000 9000
4 Seeram 750000 35000
5 Khemraj 40000 8000
6 Mohan 54000 8000
Note:
a. Gross Pay = Salary + allowance
b. Medical is 5% of salary
c. Tax is 33% of (gross pay – 35000)
d. Net Pay = gross pay – (Medical + Tax)
1. Indicate the cell and the formula to compute the gross pay for Khemraj.
A. = sum(B5:C5) or = B5+C5
2. Write the formulae that you are going to need to compute the tax paid by
Lawrence. (Hint: there will be a formula in D2 for Gross Pay. The formula for
tax in F2 will use the result in D2.)
A. = .35 *(D2 – 33000)
3. Write the formula to compute the medical paid by Seeram.
A. =B4*.05
4. Indicate the cell and the formulae required in order to compute the net pay for
Karen.
A. Cell G2. Formula = D2 –(E2+F2)
5. Write the formula to compute the total tax paid by all the employees.
A. =sum(F2:F6)
2
6. Create the worksheet in Excel and enter all the formulae in this exercise.
A.
Employee Salary Allowanc Gross Medical Tax Net
e Pay Pay
Lawrence 980000 40000 1020000 49000 325050 645950
Karen 45000 9000 54000 2250 6270 45480
Seeram 750000 35000 785000 37500 247500 500000
Khemraj 40000 8000 48000 2000 4290 41710
Mohan 54000 8000 62000 2700 8910 50390
Employee Salary Allowanc Gross Medical Tax Net Pay
e Pay
Lawrence 980000 40000 =B2+C2 =0.05*B2 =0.33*(D2- =D2-
35000) (E2+F2)
Karen 45000 9000 =B3+C3 =0.05*B3 =0.33*(D3- =D3-
35000) (E3+F3)
Seeram 750000 35000 =B4+C4 =0.05*B4 =0.33*(D4- =D4-
35000) (E4+F4)
Khemraj 40000 8000 =B5+C5 =0.05*B5 =0.33*(D5- =D5-
35000) (E5+F5)
Mohan 54000 8000 =B6+C6 =0.05*B6 =0.33*(D6- =D6-
35000) (E6+F6)
Check your progress 3
Consider the following worksheet:
A B C
1 Student Mark Status
2 Mohan 65
3 Khemraj 72
4 Triko 45
5 Adrial 35
6 Jones 40
1. Write the function to find the Maximum marks obtain by a student.
A. = Max(B2:B6)
3
2. A student is given a status “PASS” if his mark is over 60; else he is awarded a
status of “FAIL”. Write the function you would insert in cell C2 to display the
status.
A. =IF(B2>60, “PASS”, “FAIL”)
3. Write the function to find the average marks for the class.
A. =AVERAGE(B2:B6)
4. The function =TODAY() is entered into cell C10. What will be displayed in
C10?
A. Today’s date will be displayed.
Check your progress 4
1. In a new worksheet, cell E3 has the formula = SUM (B3:D3). If this formula is
copied to cell E10, what formula would appear in cell E10?
A. =Sum (B10:E10)
2. Cell E5 has the formula, = C5+$D$5. If this formula is copied to cell E10, what
formula would be displayed in E10?
A. =C10+$D$5
Check your progress 5
1. You have scrolled down your worksheet and noticed that the headings of your
columns have disappeared. What can you do to make the headings of your
columns visible at all times?
A. Lock the column title.
2. Give a scenario where you would apply a title locking on both a row and
column.
A. When you need to view both row titles and column titles in a very long
spreadsheet.
4
Check your progress 6
Consider the following worksheet.
1. What would happen if you sorted the worksheet with Status as the primary
sort field in descending order and Salary as the secondary sort field in
ascending order?
A. The spreadsheet will be sorted by the Status field in descending order first
and then by Salary in ascending order. So the status will be arranged in
order starting with technician and the salary of each technician, then
network engineers will be arranged in order with their smallest value first.
5
2. Set up the worksheet in Excel and see if your answers were correct.
A.
Salary(US$
Staff Status )
Yogesh Technician 250
Ramnarin
e Technician 325
Ron Technician 350
Adrial Technician 400
Network
Mohan Engineer 750
Network
Gregory Engineer 800
Network
Paul Engineer 850
Network
Khemraj Engineer 950
Check your progress 7
A B C
1 Employee Sex Salary Age
2 Allan Male 5600 26
3 Mary Female 3500 20
4 Peter Male 2500 31
5 Triko Female 6000 19
6 Mohan Male 8000 28
7 Susan Female 3500 22
8 Vick Male 5400 28
9 Tota Male 4500 32
10 Amrita Female 6500 35
1. Add further data to your existing worksheet to match the table above.
6
2. Filter the data to find all male employees who are over 25 years and who earn
more that 5000 in salary.
A.
Employee Sex Salary Age
Allan Male 5600 26
Mary Female 3500 20
Peter Male 2500 31
Triko Female 6000 19
Mohan Male 8000 28
Susan Female 3500 22
Vick Male 5400 28
Tota Male 4500 32
Amrita Female 6500 35
Employee Sex Salary Age
male >5000 >25
Employee Sex Salary Age
Allan Male 5600 26
Mohan Male 8000 28
Vick Male 5400 28
7
3. Find all employees who are Female or who earn less than 5000 in salary.
A.
Employee Sex Salary Age
Allan Male 5600 26
Mary Female 3500 20
Peter Male 2500 31
Triko Female 6000 19bb
Mohan Male 8000 28
Susan Female 3500 22
Vick Male 5400 28
Tota Male 4500 32
Amrita Female 6500 35
Employee Sex Salary Age
Female <5000
Employee Sex Salary Age
Mary Female 3500 20
Susan Female 3500 22
8
Check your progress 8
The number of registered IPv6 addresses by country is given in the worksheet
below:
Country Country Code IPv6
addresses
Germany DE 9500
Japan JP 8300
Australia AU 8210
United Kingdom UK 1200
United States of America US 1100
In a new worksheet, create a pie chart to show the number of IPv6 addresses
registered by each country. The pie chart should have the title, “IPv6 Addresses
by Country”. Each slice of the pie chart should be properly labeled to indicate the
country it represents or a legend should be provided. Each slice of the pie chart
should indicate the percentage of total IPv6 addresses registered.
A.
IPv6 Addresses by Country
4% 4%
34%
Germany
29% Japan
Australia
United Kingdom
United States of America
29%
9
Check your progress 9
1. Create a spreadsheet and name it ‘College.xls’. This spreadsheet should
have two worksheets named ‘Student’ and ‘Fees’. The ‘Student’ worksheet is
given below:
A.
Surname First name Sex Department Credits
Singh Mohan M Computer 32
Science
James Allan M Mathematics 20
Monroe Mary F History 28
2.
Ally Miriam F Sociology 24 The
White Michael M Chemistry 30 ‘Fees’
worksheet should have the same data as the ‘Student’ worksheet but without
the ‘Sex’ and ‘Department’ columns. Use the link formula to copy the relevant
data from the ‘Student’ worksheet. Add a column to the ‘Fees’ worksheet with
the heading ‘Tuition’ and compute the tuition paid by each student. The tuition
paid by each student is obtained by the formula, Tuition = credits * 4000.
In a blank area of the worksheet, enter the following data in different cells of
the same column:
Total Tuition
Maximum credits
Minimum Tuition
Enter the appropriate formulae in the cells to the right of the new text above to
perform the computation.
A.
Student
Surname First name Sex Department Credits
Singh Mohan M Computer 32
Science
James Allan M Mathematics 20
Monroe Mary F History 28
Ally Miriam F Sociology 24
White Michael M Chemistry 30
10
Fees with summary
Surname First Credits Tuition
name
Singh Mohan 32 128000
James Allan 20 80000
Monroe Mary 28 112000
Ally Miriam 24 96000
White Michael 30 120000
Total Tuition 536000
Maximum
credits 128000
Minimum
Tuition 80000
Formulas for Fees
Surname First name Credits Tution
Singh Mohan =Student!E2 =C2*4000
James Allan =Student!E3 =C3*4000
Monroe Mary =Student!E4 =C4*4000
Ally Miriam =Student!E5 =C5*4000
White Michael =Student!E6 =C6*4000
Total Tuition =SUM(D2:D6)
Maximum
credits =MAX(D2:D6)
Minimum
Tuition =MIN(D2:D6)
11
Check your progress 10
1. Create the following spreadsheet starting at cell A1 and save it as
‘Course.xls’.
Semester 1 Semester 2 Summer
4 4 2
2. Create a new spreadsheet starting at cell A1 with the following data and save it as
‘Credit.xls’.
Semester 1 Semester 2 Summer
16 12 4
3. Open the spreadsheet ‘Course.xls’ and import the spreadsheet ‘Credit.xls’ to
begin in cell A10.
A.
12
End-of-section questions
Multiple choice questions
1. A group of adjacent cells in a spreadsheet is known as:
A A worksheet
B A template
C A range
D A record
A. C
2. All text entries into the spreadsheet are known as:
A Values
B String
C Labels
D Characters
A. C
3. By default, numerical entries into the spreadsheet are aligned:
A Left
B Right
C Center
D Justified
A. B
4. If cell address contains $B$7, it is what type of a cell reference?
A Mixed
B Relative
C Absolute
D Combined
A. B
5. All of the following are formatting of numeric values in a spreadsheet except:
A General
B Custom
C Percentage
D Value
A. D
13
Structured questions
1. Give the meanings of the following terms as they relate to a spreadsheet:
a. Cell
b. Formula
c. Range
d. Cell address
A. a. An intersection of a row and column in a spreadsheet.
b. Calculations involving cell addresses and mathematical operators. All
formulas begin with = sign in Microsoft Excel.
c. A group of adjacent cells in a spreadsheet.
d. An identification of a cell, which is a combination of column letter
followed by row number.
2. a. List five commonly used spreadsheet functions.
b. State the purpose of each of the functions you identified in your answer to
Q2a.
A. a. =sum( ); =average( ); =max( ); =min( ); =count( ); =today( ); =rank( )
b. =sum( ) – calculates the total of a range of cell values
=average( ) – finds the average of a range of cell values
=max( ) – finds the largest value among a range of cell values
=min( ) – finds the smallest value among a range of cell values
=count( ) – finds the number of numerical values in a range of cell
values
=today( ) – displays today’s date
=rank( ) – displays the position of a numerical value in a range of cell
values
3. a. State the difference between relative referencing and absolute referencing.
b. Give one example of a cell with relative referencing.
c. Give one example of a cell with absolute referencing.
A. a. Relative referencing or addressing is where the cell address changes
automatically when they are copied to another cell but in absolute
addressing the cell address becomes absolute or fixed.
b. A5
c. $A$5
14
4. a. List five types of formatting that can be applied to numeric values.
b. State the purpose of each of the types of formatting you identified in your
answer to Q4a.
A. a. Currency; Number; General; Percentage; Comma; Accounting
b. currency – to display numeric values money format with currency
symbols
number – to display numeric values in number format with or
without decimal places
general – to display numeric values in number format without
decimal places or commas separating thousands
percentage – to display numeric values in percentage format with or
without decimal places
comma – to display numeric values in number format with commas
separating thousands, with or without decimal places
accounting – to display numeric values in money format with the
currency symbol aligned to the left and numbers aligned to the right
5. List the steps in creating a chart in spreadsheet.
A. Step 1:Select the range of cells for which the graph is to be created. If
there are non-adjacent cells use the Ctrl key from the keyboard to select
multiple sections.
Step 2: Choose Insert Chart options from the menu bar to display the
chart wizard window with list of chart types.
Step 3: Choose the chart type required and choose Next.
Step 4: Choose the data range and choose Next.
Step 5: Choose appropriate tab to enter titles, legend, data labels, etc.
based on the type of chart required.
Step 6: Choose Next to get the chart location.
Step 5: Choose the location and choose Finish.
15
