Lab-Chapter 6

# Lab-Chapter 6 - CSE 1520.03 Computer Use Fundamentals The...

This preview shows pages 1–3. Sign up to view the full content.

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

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.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern