CSE 1520.03
The Glade
Computer Use: Fundamentals
Laboratory Handbook
Chapter 6:
Database Features of a Spreadsheet
Objectives
This laboratory exercise introduces the database functions of Excel and builds on skills
you have learned in Chapters 1 through 5. This lab also puts much more emphasis on
your ability to analyse and design your own solution to the problems presented.
This lab will cover:
The COUNTIF and SUMIF functions
Sorting data and creating subtotals
More Date functions
The MATCH, VLOOKUP, HLOOKUP and INDEX functions
Preparation
Read the whole of the chapter before beginning the exercises.
Exercise 1
– Frequency Counts
An operation that is often performed with a large set of data is to find out how many of
each value occur in the data. For example, teachers usually need to count how many
students received 50 on a test, how many received 51 etc. in order to construct a
frequency bar chart or distribution.
Open
Exercise 1 (Lab6_Ex1)
in
Support Files
(Chapter6) of the course website.
This model consists of three worksheets  the
Comments
worksheet, a worksheet
called
Numbers
and a worksheet called
Counts
. The
Numbers
worksheet simply
contains a long list of numbers between 1 and 20 inclusive. Your task will be to calculate
in the
Counts
worksheet how many times each number (1, 2, ... 20) occurs. There are
several ways of doing this – but we will limit discussion to one that is common to most
spreadsheet programs.
The COUNTIF function
The COUNTIF type of function is quite common to spreadsheet programs, although the
exact name of the function might not be COUNTIF in other spreadsheets. As its name
suggests there are 2 parts to the function. The COUNT will be done only IF some
criterion is satisfied. In this case the criterion will be "is the value = 1"  in which case
increment the count for 1s; or "is the value = 2"  in which case increment the count for
2s; and so on.
61
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
The Glade
CSE 1520.03
Computer Use: Fundamentals
Laboratory Handbook
To use this function you first need to create a column containing the criteria. So in the
Counts
worksheet enter an appropriate column heading and then the list of possible
values that might be found in the
Numbers
worksheet, i.e. 1 through 20. In the cell next
to each of these numbers you will enter the formula which produces a count of how many
times that value occurs in the
Numbers
worksheet.
Enter an appropriate heading for the new column and define names for the data ranges.
Begin to enter the formula in the first cell (which you'll fill down the column). Use the
function wizard to select the
COUNTIF
function from the
Statistical
group of
functions. The arguments required are a Range and a Criteria.
The
Range
is simply the column of numbers in the
Numbers
worksheet. If you simply
drag over the column in order to enter the argument in the
Range
text box you will see
the range specified as
Numbers!A2:A301
which is going to lead to an error unless you modify it. Can you anticipate what the error
will be? Do you know the best way to avoid it?
This is the end of the preview.
Sign up
to
access the rest of the document.
 Fall '09
 PAUL
 laboratory handbook

Click to edit the document details