Normal probability plots.
is quite easy to make a normal probability plot with Excel. In this
exercise we generate
100 observation from the standard normal distribution and make a normal probability plot
of the data.
To start we generate those 100 observations from N(0,1):
Random Number Generator window from the Data Analysis toolbox and fill
the options as to
generate 100 observation from N(0, 1) in the cells A1:A100.
Sort the data in ascending order.
Next you will create in the column B a
sequence of 100 z-scores such that in-between each two of them the area
under the Standard Normal Distribution curve to be equal with 1/100 i.e. 1/(the number of observations). It is
quite easy to create such a sequence for N = 100, but you will do it in such a way that you may use the worksheet
for any other data set with with any number of observations
Put the number 0.5 in the cell D1 and put 1.5 in the
Highlight both cells. Then the screen should look something like:
Turn this into a long column of counting numbers, 0.5, 1.5, 2.5,...,99.5,
by using the mouse
to pull the little square (on the lower right hand corner of the highlighted box) downwards to the entry D100.
Be sure that you highlight both cells before dragging down, otherwise you will get only 1.5s!
Highlight the cell C1, then type the following formula:
Drag the right little square of the cell C1 down to the C100. You got the grid of percentile values.
Next we compute in the column B the z-scores
of the values from column C.
the cell B1 type:
Drag the right little square of the cell
down to B100. The first rows should look like
in the image above.
You may check that you got the right sequence of z-scores by plugging in the worksheet from Ex.1.82 the values
from the cells B1:B2 (or any other two adjacent cells from the column B) and observing the resulting output:
Note. The numbers
from column A are different for every random sample.
But it may happen that you'll get exactly
the same sample as in the image if you just started Excel.
Now you will make the normal probability
Highlight the cells A1:B100. Choose Chart, then
and the first sub-type. Click "Next"
and choose the "Series" option and choose the column B for the X-axis and column A for Y-axis. (You want
to have the z-score on the X-axis. Click "Next" to get to the "step 3 of 4" and chose to have grid lines for both axis:
The the normal probability plot will appear. Clear (delete) the legend
and format the
axis as below.
Be sure that your chart is square shaped
for the best display of the plot.
As you observe the this normal quantile
plot lie almost on the diagonal as you expected to be because your data
from the column A is sampled from the Standard Normal Distribution.
Reload data in the column A ten times with
the Random Number Generator . Each time you will
get a similar diagonal plot!
Hand in an Excel worksheet with a normal quantile plot for the guinea
pig survival time
from table 1.5. in a format similar to the above image.
Note. The Y-axis should have the range [0, 600] as in Fig 1.38 from the textbook.
briefly how this data set deviate from normality.