Statistics
Objectives:
1. To be able to make frequency distributions and relative frequency distributions.
2. To be able to make bar graphs, pie graphs, histograms, and scatter plots.
Procedure:
Check your e-mail for the data to use for your excel assignment. (Note: You will only
being using worksheet 1 for this assignment. You will not be using worksheet 2.)
You should see data for several houses listed for sale in Niagara County. This data i
includes information on the location of the house, the number of bedrooms, the year that
the house was built, the number of square feet, and the price of the house.
You will need to go to “File”. Click on “Options”. Then, click on “Add-Ins”.
Highlight “Analysis ToolPak” and click on “Go” located at the bottom of the page.
Check the boxes next to “Analysis ToolPak” and “Analysis ToolPak – VBA” and click
on “OK”.
Create a frequency distribution anda relative frequency distribution:
1. In cell G1, type: town/city. In cell G2 to 613, type each town/city
(Lockport, Sanborn, Ransomville, Youngstown, Wilson, Gasport, Newfane,
Barker, Middleport, Niagara Falls, North Tonawanda, and Lewiston.).
2. Column H will include the frequency for each town. In cell H1, type:
frequency.
a. To determine the number of houses listed for sale in Lockport, type in cell
H2: =countif(A1:A105, G2). This will count how many houses are listed
from items A1 to A105 that are from cell G2 (Lockport).
b. You will need to do this with each city/town. (NOTE: you will type:
=countif(A1:A105) for each city/town. Then, you will type the cell that
lists the town.)
3. To construct a relative frequency distribution, you need to find the sum. In
cell H14, type: =sum(H2:H13). This will add the numbers in cell H2 to H13.
4. Then, in column I, find the relative frequency distribution.
a. In cell 11 , type: relative frequency.
b. In cell 12, type: =H2/Hl4. This will find the relative frequency for
Lockport.
c. Find the relative frequency for each town] city.
Construct a bar graph for the towns.
1. To do this, you will need to highlight columns G and H.
2. Click on “Insert” located at the top of the page. Click on “Bar Graph”.
3. Note: If you right click on the title, you can edit the title.
Construct a pie chart for the number of bedrooms.
1. In K1, type: bedrooms. In K2 – K8, type: 2 bedrooms, 3 bedrooms
2. In L1, type: frequency. In L2, type: =countif (B2:B105, 2) to find the
frequency for 2 bedrooms. Find the frequency for bedrooms 3 through 8.
3. Highlight columns K and L.
4. Go to “Insert” and click on the pie graph. Edit the title.
Construct a scatter plot on the number of square feet and the price of the house.
1. Highlight the 2 columns for the square feet and the price (Columns D and E).
2. Go to “Insert” and click on the scatter plot graph. Edit the title.
Construct a histogram for the prices of houses.
1. First, you will need to type the bins. In cell N1, type: bins. In cell N2, type:
50000 and then in each cell increase by 50,000. (50000, 100000 .. .) End
with 1,000,000.
2. At the top of the page, click on “Data”. Then, click on “Data Analysis”.
Under “Data Analysis”, click on “Histogram”. Then, click on “OK”. Type
in the range of the data (E2zE105) and the bins (N 2zN21). Check the boxes
for “New Worksheet Ply” and “Chart Output”. Then, click on “OK”.
3. The histogram should have no gaps. To do this, right click on the rectangles
in the histogram and select “Format Data Series” and under “Series
Options”, move the arrow over to “No Gap”. The gap width should be 0%.
Edit the title.
Construct a histogram for the year that the house was built.
1. Use column P to type the bins. In cell Pl, type: bins. In P2, type: 1875 and
then in each cell, increase by 25 years. (1875, 1900, 1925…) End with 2025.
2. Follow the steps for creating the histogram for price. Use (C2:C105) for the
range and use (P2:P8) for the bins.
3. Follow the steps for creating the histogram for price so that there are no gaps.
Analyze each graph (Type your report in Microsoft Word):
1. Analyze both the bar graph and the pie graph. What do the bar graph and the pie
graph tell you about the location and the number of bedrooms?
2. Analyze the scatter plot. What type of relationship (positive, negative, or no
association) exists between the number of square feet and the price? What does
this relationship tell you about the number of square feet and the price of the
home?
3. Analyze the histograms. What does the histogram show you about the prices of
homes? What does the histogram show you about the years that the houses were
built?
4. End your report with a concluding paragraph on what you learned about the
housing market.