MPP 806Lab Assignment Fall 2009

Comparing Distributions

[A] Skippy’s grades.

Skippy earned grades in two courses:

69% in MPP 601 (statistics)and73% in MPP 622 (Canadian Culture)

It “appears” that Skippy did better in MPP 622 than in MPP 601. However we want to verify that fact. To do this we need to:

  1. Calculate the Mean and Standard Deviation for each class
  2. Normalize the grades so we can draw a comparison

Step 1:

Download and open the excel file called “Stats_hw_1.xls” and select the sheet called “Two Courses”:

You will see the grades for the two courses in Columns B and F. There are 141 students in each class. You will see Skippy’s name and grade for each class (Row 8)

Step 2: Find the mean and standard deviation.

In cell B2 enter the formula =AVERAGE(B6:B146)

In cell B3 enter the formula =STDEV(B6:B146)

You should get the values:

Mean / 48.2539007
St. Dev / 15.3212855

Use the formatting commands to reduce the decimal to 2 places (i.e. 49.99) in each case.

Now, “copy and paste” cells B2 and B3 into cells F2 and F3.

Question: Are the Means the same? What about the Standard Deviations?

Step 3: Normalize the grades for both courses

To normalize grades use the formula

Z = (x – 

where xis the gradeis the mean; andis the standard deviation.

In cellC6 type the formula =(B6-$B$2)/$B$3

NOTE the $ in $B$2 and $B$3; these are “absolute cell references” (remember?)

Copy this formula all the way down the column (to C146)

Repeat the steps in Column G (for course MPP 622).

Question: What is Skippy’s Z score for each course? What does this tell you? (hint: ask Stephen)

[B] Descriptive Statistics

We now want to look at summary statistics for each course

At the top of the excel sheet, click on the tap “Data”

Note on the right hand side there are two items: “Data Analysis” and Solver”

(if missing, get assistance on “Adding” these items)

Click on “Data Analysis” and you get the dialog box

Select Descriptive Statistics

Fill it in so that it looks like:

If you did everything correctly, you should jump to a NEW page with the following:

You may have to re-size the columns

Repeat the steps for the other course (MPP 622)

[C] Histograms

A useful way to look at data (such as grades) is with a Histogram. This is very similar to using the charting option in Excel, except we will be asking Excel to group the data in ranges and create the graph.

Step 1:

BINS: the first step to using a Histogram is to determine your ranges by creating a “BIN”

Go to Cell I5 and type “BIN”. Then, in each cell below (I6, I7, etc) enter numbers. Start at 0 and go up by 10’s until you reach 100. This is your BIN

STEP 2:

Click on the “Data” Tab and then click on the “Data Analysis” button. This time you will select “Histogram”

You will get the following dialog box:

Enter the appropriate information for the first course. When done you should have the following:

BE SURE TO SELECT “CHART OUTPUT” IN THE BOTTOM CORNER!

Once you click OK, a new sheet will be created that has the following:

Repeat the steps to create a Histogram for the other course (622-Hist)

You should get a histogram that looks like:

This Chart does not appear to have much information: Two big bars and one small one. The problem is that our “BIN is too “coarse” Lets re-do the Histogram for MPP 622 using a “finer” bin (intervals of 5)

In Cell J5, type BIN2 and below add increments of 5 (see picture)

Now, re-do your Histogram for MPP 622.

Enter the following:

Your Histogram should look like this:

FINAL Questions:

Look at your Histograms AND your Descriptive Statistics sheets. Does the info in the descriptive Statistics page correspond to your Charts? Consider each Item (Mean, Median, Mode, Kurtosis, Skewness)

There is a DEMO of a normal Distribution found in the spreadsheet for comparison

1