17 Pages
Unlock Document

Electrical Engineering and Computer Science
EECS 1520
Peter Yong Lian

CSE 152003The GladeComputer Use FundamentalsLaboratory HandbookChapter 6Database Features of a SpreadsheetObjectivesThis 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 presentedThis lab will coverThe COUNTIF and SUMIF functionsSorting data and creating subtotalsMore Date functionsThe MATCH VLOOKUP HLOOKUP and INDEX functionsPreparationRead the whole of the chapter before beginning the exercisesExercise 1Frequency CountsAn 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 distributionOpen Exercise 1 Lab6Ex1 in Support Files Chapter6 of the course website This model consists of three worksheetsthe 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 220 occurs There are several ways of doing thisbut we will limit discussion to one that is common to most spreadsheet programsThe COUNTIF functionThe 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 value1in which case increment the count for 1s or is the value2in which case increment the count for 2s and so on61The GladeCSE 152003Computer Use FundamentalsLaboratory HandbookTo 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 ie 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 worksheetEnter an appropriate heading for the new column and define names for the data ranges Begin to enter the formula in the first cell which youll 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 NumbersA2A301which 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 itThe criterion for this first cell is that the value in the Numbers worksheet be equal to 1 ie the value in this first row of the Counts worksheet Look through the help pages on this function by clicking on the help icon in order to read about the possible ways to specify the criteria argumentHelp icon in function argument panelThe criterion that you specify in this case is simply the reference to the cell containing the number 1ie the cell adjacent to the one containing this formulaFinish the formula and check that the answer you get is that there are 15 1s in the Numbers worksheet Next simply fill the formula down the column Did you correctly enter the Range argument or did you run into the error you were warned about above The results for the first few counts are shown in Figure 61Figure 61part of the frequency counts using the COUNTIF function62
More Less

Related notes for EECS 1520

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.