Motivation: All too often students learn new mathematical ideas without
having the resources available to make those ideas meaningful. This lab is designed
to be used thematically between the social studies, technology,
and math classes. This lab gives students a chance to compare what they believe
about wealth to what the statistics imply.
Objectives: Students will develop an application for statistical concepts
by applying statistics to common values based discussions in economics. Students
will use technology to analyze data. Students will recognize that math does
not always result in one single answer, but instead becomes the basis for discussion.
Background: This lesson was used in an inner-city high school. The lesson
was coordinated between the math and technology classes so that students would
finish their graphs and discussions in the computer lab in time to be discussed
in the math class.
It is suggested that the social studies and math teacher modify the questions
at the end, and the procedures to support those questions to the skill level
of the classes. Otherwise, cut and paste starting here:
Statistics and Economic Power
Basic Spread Sheet Project
In this project, you will organize the IRS statistics of Income and create
graphs to focus on the questions: Who has great economic power? Who does
do not? How much economic power do those at the top really have?
To do all this, you will use the standard concepts of statistics such as, median,
mean, percentile and variance. You will also use the standard tools of
statistics including charts, graphs, and spread sheets.
Part 1: Acquiring the data:
Create a folder called IRS Data on your tech lab network disk.
Go to your folder and click on this file. This will create 4 files.
Part 2: Median, Percentile, & Quartile:
In this section you will calculate and interpret median, percentile and quartile.
A: Setting up the file:
Create an Excel file called Percentile
Put your name in cell A1
Open the IRS file INDT1
Copy and paste the first 3 column sections from INDT1 (cells A6 to C31)
into your file
Raise the columns so that you can see the numbers. Remember the symbol:
######## means that a number is too large to be seen in a column this narrow.
B: Organizing the data:
Examine the 3 columns you have copied. The first column is a text field
reporting ranges of income. The second column is a number field reporting
count (frequency) of taxpayers within this range. The third column is
number of thousands of dollars earned within this range. For our calculations
this organization is not very useful. We will need to calculate some more information.
To calculate percentile will take 2 columns. First, in column D corresponding
to no income (cell D15 if you copied to the same cells copy over the number
of return with no income ( use: =B15)
From here we want to create a column adding all the lower numbers. To do
this, in the next cell (D16) add the cell from row B with the cell above (use:
Above the numbers in column D put the label "Integral"
Copy and paste this formula into the cells below, until you reach the million
dollar range. (D29)
Now you may calculate percentiles. In each cell in Column E calculate percentile
by dividing the value in D by the total returns (B14) and multiplying by 100.
E.g.: cell E15 gets = D15/B14*100
Format column E to have 1 digits after the decimal place.
Check that the list goes from 0 to 100.
Above the numbers in column E put the label "Percentiles"
Now in column F we will determine the average income for each range.
This will be done by dividing column C by column B and multiplying in the
1000 (e.g.: =C15/B15*1000 )
Label column F as "average income by range"
Section C: Graphing the Data
Here we will create graphs to represent the data.
Highlight the numbers in columns E & F
Click on the graph symbol
Pick X-Y scatter and pick the first option with curves.
On the titles page, label the x-axis as: "Percentile of Wage Earner"
and label the y-axis as "income."
Create a second graph, using all but the last 4 values (highlight E15 to
Section D: Using Data and Graphs to Reason about Economic Power
Use the charts and graphs you created to answer the following questions.
What is meant by the negative number for income correlating to the range:
"No adjusted gross income"?
Estimate the Income of the Median (50th percentile), 1rst quartile, and
What range of incomes do you think correspond to economically poor? What
percent of the population is in this range?
What range of incomes do you think corresponds to economically "rich"?
What percent of the population is in this range?
For the two graphs you made, why is the second graph better for viewing
low incomes, and the first only good for large incomes?
What is meant by "adjusted gross income?" Could this definition
affect the quality of the data?
Compare different methods of determining range or variance for this data,
e.g.: the gaussian formula, throwing out the outlyers, natural groupings,
... Which method do you think has the most meaning for this data?