Title: Curricular Unit: Using MS Excel to Analyze Real Life Data
1Curricular Unit Using MS Excel to Analyze Real
Life Data
Economics 553 Assignment 3
By Fadel Sesay
- Summary
- This lesson involves the use of Microsoft Excel
to analyze real life data. The approach used in
teaching this lesson is based on the notion that
students learn best by doing. Students will
gather some real life data by measuring their own
heights and weights and recording the information
in an MS worksheet. Students will then analyze
the data in MS Excel to see how height relates to
weight.
2Objective
At the end of this lesson students will be able
to
- write formulas to do simple arithmetic
operations in Excel
- find the average of a set of numbers in
MS Excel
- convert from inches to feet
- graph the relationship between two variables
3Requirements for this Activity
- basic knowledge of MS Excel
- an understanding of the metric system
- ability to use the Internet (required only for
electronic textbook referencing)
- access to a computer with Internet access
(required only for electronic textbook
referencing)
- a scale for measuring weight and a tape for
measuring height
4Student Activity
Duration of Activity
This activity is expected to take five class
periods or one week.
Day 1 Overview of MS Excel
Go over some of the basic things students need to
know in order to complete this exercise.
http//www.cant.ac.uk/title/Excel/Part1/ExcelPrt1.
htm
This site offers a tutorial on getting started
with Excel entering text and numbers in a
worksheet, saving and printing.
5Student Activity (Contd.)
- Open up MS Excel and create the following
workbook
- Each student will weigh himself/herself and
measure his/her height and four other students
and then record the data in an Excel worksheet
like the one shown below. This will probably
take some time, depending on the class size.
6Student Activity (Contd.)
- Write your name and the names of those on
whom you will gather data in the name column
of your Excel workbook.
- Weigh yourself on a scale and enter your
weight (in pounds) in the Excel worksheet. Find
a partner and have him/her measure your height
(in inches) and enter the data in the worksheet.
Record the weight and height of four other
people in your workbook.
- At home, spend a couple of hours practicing
how to use Excel and how to make a simple
Excel workbook like the one we made in
class. If you need help on how to create the
worksheet, visit the following web site and
click on Getting Started.
http//is.rice.edu/consult/cross/excel/
NOTE if you dont have a computer at home use
the school or public library.
7Student Activity (Contd.)
Day 2 Measuring and Recording Data
- Your recorded data will look like the
following
8Student Activity (Contd.)
- Next, add a new column to your worksheet
starting from cell E1 and name it HEIGHT (in
Feet).
- Create a formula to convert the first persons
height from inches to feet. The formula should
look like (D4/12), which means divide the
contents of cell D4 by 12 (since 12 inches 1
foot).
- Repeat the formula for the other names
(persons) in your workbook to calculate the
corresponding weights. You can use AutoFill to
copy the formula to the other cells.
- Spend a couple of hours practicing by yourself
at home or in the library. For help on entering
formulas in Excel, visit the following web site
http//www.cant.ac.uk/title/Excel/Part2/ExcelPrt2.
htm
9Student Activity (Contd.)
Day 3 Calculating Averages and Checking Result
- Type AVERAGE at the bottom of the Name Column
in your workbook.
- At the bottom of the WEIGHT column (in the
AVERAGE row) enter a formula to calculate the
average weight for the persons on your list. For
example, average(c4c8).
- At the bottom of the HEIGHT column (in the
AVERAGE row) enter a formula to calculate the
average height for the persons on your list. For
example, average(d4d8).
- Manually calculate the above averages to check
the results you got using Excel.
10Student Activity (Contd.)
- Your workbook should now look like the following
11Student Activity (Contd.)
- Outside of class, visit the following web site
to learn more about some of the practical
applications of Excel. Spend about two hours on
this.
http//www.lacher.com/toc/tutbusiness.htm
This site offers a tutorial on how to use Excel
in a variety of business applications.
- Assignment Using the year 200 calendar, enter
the number of days for each month, finding the
total number days in the year 200 and the
average number of days in a month by creating
formulas in Excel in an Excel worksheet. (1 hour)
12Student Activity (Contd.)
Day 4 Filtering the Data
- Select all the data in your workbook, including
the column headings.
- On the menu bar, click on Data, select Filter,
and choose AutoFilter. A small box with an arrow
will appear at the lower right hand corner of
each top cell.
13Student Activity (Contd.)
- To filter for the female data, in the
FEMALE/MALE column, click on the small box
and a menu will appear.
14Student Activity (Contd.)
- Select F from the menu and your worksheet will
now show only the female data, like the
following
15Student Activity (Contd.)
- You may copy and paste the female data into a
separate worksheet to have a separate data set
for the females.
- Click on the filter box in the FEMALE/MALE
column, and select All. The entire data will
reappear.
- Outside of class, practice filtering the data
for males.
16Student Activity (Contd.)
Day 5 Graphing the Data
- To graph the weight and height data together,
select the NAME column, hold down the Ctrl key
and select the WEIGHT column then the HEIGHT
column.
- Click on Insert on the menu bar and select
Chart. The chart wizard will appear. Select the
chart type and click Next and then Finish.
17Student Activity (Contd.)
- Plot separate charts for the Male and female
data sets, respectively, by yourself.