Note: Before you begin this activity, you will need to load
the free Data Analysis Toolkit. This is an add-on built into Excel. Depending
on which version of Excel you have, the steps differ slightly. View this
tutorial (Links to an external site.) for details. (or go re-watch the video in
Module 7 demonstrating how to add the Data Analysis Toolkit to Excel.) You do
not need to download or pay for anything, just activate this included add-on in
MS-Excel.
Frequency information is a great way to summarize data. In
essence, you create bins with ranges and using a Histogram to show how many
data points fit within each bin. An example of when this type of data is useful
is viewing organizational salary structures and how staff members earn certain
salary ranges. Save the following MS-Excel worksheetPreview the documentView in
a new window (.xls) to your local computer and open the worksheet in MS-Excel
or equivalent.
In this exercise, you will determine the salary range
distribution of all occupations within the state you chose. Using the Bureau of
Labor Summary Statistics create a histogram by doing the following:
Choose a single state you would like to live in (e.g.,
family there, etc.). Filter the data to show only that state.
Proof the data in the Annual Mean Wage column and make sure
that only numerals are present. If a * or # is present, delete that value and
leave that cell blank. A histogram cannot include non-numerical data in Excel.
Create the bin ranges to the right of the data. See the
example. Use the bin ranges present in the example.
Click on Data Analysis in the Data tab. If this toolkit is
not present, the Data Analysis Toolkit may not have been properly installed.
tool_bar(1).png
Select Histogram.
Enter the input range. Since you have filtered the data, you
cannot simply select the Annual Mean Wage column. You must select the first and
drag to the last data point.
Enter the bin range. Select the range that includes the bins
you wish to create. You should have bins already in your worksheet from Step
#3.
Enter the output range. This is where you want your
histogram and frequency data to appear.
Click OK.
Here is an example for creating histograms in Excel. Another resource is provided below for
assistance.
(Note: The example does not edit chart titles, axis titles
etc. This example simple creates a
histogram; for you projects you may want to make the necessary edits to any
charts you create.)
What does this data
tell you? Answer the following questions:
Is the data normally distributed, skewed right, or skewed
left? What does that mean? For the data used does it make sense?
What does the histogram tell you about the “middle class” in
the state you chose?
What does the histogram tell you about the income
distribution in that state?
See the following exampleView in a new window (.xls). This
is just one of several ways to complete this activity. It’s okay if your
deliverable looks slightly different. There are many chart formats to choose
from. Create what makes sense to you and what you find visually appealing.
Note: This example
worksheet is locked and can only be viewed.
Hint: If you need assistance with using Excel for this
assignment, access Lynda.com via ERNIE and search for a course titled Excel
2007: Business Statistics with Curt Frye. You do not need to watch the entire
tutorial. Scroll down below the presentation and watch the brief module that
applies to your assignment. Module 3, Creating a Histogram seems most useful
here. This module is not required for this assignment and no completion
certificate is required. But, this module may be useful to students who are not
proficient with Excel.
Prior to submitting your assignment, please use the
following naming convention for your file: Lastname_Activityname (e.g.
Smith_Frequencies_Histograms).
Review the Problem Set (Quantitative Only) Rubric below. It
will be used to evaluate this activity.
