This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 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. 6-1 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....
View Full Document
This note was uploaded on 12/09/2010 for the course CSE CSE 1520 taught by Professor Paul during the Fall '09 term at York University.
- Fall '09