Chapter 4 Lab 4: Normal Distribution & Central Limit Theorem

By a small sample, we may judge of the whole piece. —Miguel de Cervantes from Don Quixote

4.1 General Goals

  1. Distributions
  2. Normal Distribution
  3. z-scores

4.2 Excel

4.2.1 Goals

In this lab we will construct an excel worksheet that will:

  1. Answer forward z score transformation problems
  2. Answer reverse z-score transformation problems.

The worksheet we make today will be a general purpose tool that will solve Z-score problems for any data set you input to it, and will hopefully make it much easier for you to answer the many Z-score problems included in your homework this week.

4.2.2 Load the data

4.2.2.1 Creating a blank worksheet

Today we will start by creating a blank workbook in excel

  1. Open Excel
  2. Select “blank Workbook”

You should now have a blank worksheet open, ready to be turned into a Z-score calculating machine.

4.2.2.2 Entering our practice data

  1. In cell A1 (the top left cell) enter the word “input”
  2. Below that, in column A enter the following values:

92 91 75 87 62 60 98 58 50 63 93 72 93 88 79 85 84 78 86 75 93 65 85 84 82 85 84 84 90 79 84 95 65 80 84

These values are student’s grades on a test, marked out of 100. We will use this data to build and test this worksheet, but this data can be replaced by any sample data, and this worksheet can still be used to calculate Z-scores and proportions.

4.2.3 Calculate descriptive statistics

  1. in cell C1 write “average”
  2. In cell C2 write “standard deviation”
  3. in cell D1 enter:

=average(A:A)

  1. In cell D2 enter:

= stdev.s(A:A)

The formulas will update for any set of data you paste into the A column.

4.2.4 Calculate Z score of x-value

  1. Make a cell where you will place your X values. I will put my x value in cell D3, so I place my “x” label in cell C3
  2. Lets enter a value of “80” into cell D3 for now.
  3. In row 4, write “Z-score” column C
  4. In cell D4 we will place our Z-score formula:

=( D3-D1)/D2

When you press enter, your result should equal -0.01941. This is the Z-score of a x score of 80, given this set of data. Why does it make sense that we got a small negative value?

4.2.5 Calculate percentage of the normal curve above and below X

  1. in cell C6 write “% below X” and in cell C7 write “% above X”
  2. In Cell D6:

=NORM.S.DIST(D4,TRUE)

  1. In cell D7:

=1-NORM.S.DIST(D4,TRUE)

These two cells will now tell you the percentage of a normal curve above, and below your entered x value. Changing either the X value, or the sample data in column A, will cause these values to update automatically, which I bet you will find very useful doing your homework this week.

4.2.6 Reverse Z score calculator

Now we will create a calculator in columns F and G that will find the raw score that corresponds to a given percentage.

  1. In Cell F1 write “% below x”
  2. In Cell F2 write “x score”
  3. In Cell G1 enter 50 (this is only a test value, and you can change it to different percentage values)
  4. In Cell G2 write

=NORM.S.INV(G1/100)*D2+D1

This should equal 80.22857 when you press enter. That’s the mean of our set! Why does it make sense for us to get this value?

4.2.7 On Your Own

Answer the following questions:

  1. For our forward Z-score calculator, we used a test value of 80 and got a “% below X” of .492253. Why does this value make sense given our sample data?

  2. For our reverse Z score calculator, we used a test value of 50%, and got an x score equal to the mean of our sample data. Why does this make sense?

  3. Write a short set of instructions explaining how this z-score calculator can be used to answer Z score questions form the homework or textbook.

Extra Credit:

Modify the forward Z score calculator to find the percentage of the normal curve between 2 x values.