DATA ANALYSIS
(SPREADSHEET)
LESSON 5(CHAPTER 20)
Learning Objectives
By the end of this chapter, you will be able to:
Spreadsheet
Basics
Create a
Data Model
Task A : Creating a simple Data Model
Create a spreadsheet to add, subtract,
multiply, and divide any two numbers
together and display the result.
Save as “Data Analysis – Task A”
F4
Using
Formulae
Task B : Using Formulae
Open the file OPERATORS.CSV
Place two numbers of your choice in cells B1 and B2.
Calculate in cell.
● B4, the sum of the two numbers
● B5, the difference between the two numbers
● B6, the product of the two numbers
● B7, the contents of cell B1divided by the contents of
cell B2
● B8, the contents of cell B1 to the power of the
contents of cell B2.
Save as “Data Analysis – Task B”
Named Cells
and Ranges
Task C : Named Cells and Ranges
Open the file SALES.CSV. This spreadsheet will be
used to calculate a bonus payment to sales staff for a
small company.
● Name cell B1 ‘Unit’.
● Name cells A5 to C7 ‘Rate’.
● Names cells B11 to G18 ‘Sold’
Save as “Data Analysis – Task C”
Using
Functions
Task D : Using Functions
Copy this spreadsheet model and then calculate:
● The total (SUM) number of hours worked by all of
these five people
● The average number of hours worked per person
● The maximum number of hours worked by any of
these five people
● The minimum number of hours worked by any of
these five people.
Save as “Data Analysis – Task D”
Task E : Using Functions
Aaron Kane did an extra four hours’ work. Change
the spreadsheet you saved in Task D to show the new
figures.
NOTE: The manager wants to see the average
number of hours worked displayed as:
● An integer value
● Rounded to the nearest whole hour
Save as “Data Analysis – Task E”
Task F: Using Functions
Open the file COUNT.
This file lists some workers and the number of jobs
they have still to finish for a project.
Place a formula in cell A22 to count the number of
workers that still have jobs to be finished for the project.
Place a formula in cell A24 to count the number of
workers on the project.
Save as “Data Analysis – Task F”
Using
Lookups
Task G: Using Lookups
Open the file TUTORS.CSV.
Insert formulae in the Tutor Name column to look up
the tutor’s name by matching the tutor’s initials to the
file TEACHERS.CSV
Insert formulae in the Room Number column to look
up the room number by matching the tutor’s initials to
the file ROOMS.CSV. (This formula is HLookup not
VLookup)
Make sure that you use appropriate absolute and
relative cell referencing.
Save as “Data Analysis – Task G”
IF
Functions
Task H: IF / Nested IF Functions
Open the file Project 153.Excel
● Add a new label ‘Category’ into cell D2.
● Place formulae in cells D3 to D21 to display ‘Very experienced’ for employees
with ten years or more experience.
● For every other employee (those with less than ten years experience) the
formula should display ‘Not experienced’.
● Add a new label ‘Category2’ into cell E2.
● Place formulae in cells E3 to E21 to display:
○ ‘Not experienced’ if they have less than five years
○ ‘Experienced’ for employees with five years or more experience.
○ For every other employee (those with ten or more year’s
experience) the formula should display ‘Very experienced’.
Save as “Data Analysis – Task H”
Manipulate
Data
Task I: IF / Manipulate Data
Open the file ICT EXPERT.Excel
1. Interrogate the data to search for the employees who are currently working
on jobs for Binnaccount.
2. Interrogate the data to search for the employees where the task code in
between three and six.
Save as “Data Analysis – Task I”
