***************************
  Exercise  1.104.
***************************

Normal probability plots.
***************************

It 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):

Open the 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

First,

   Put the number 0.5  in the cell D1 and put  1.5 in the cell D2.
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!

Now,
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.
 

In the cell B1 type:
 


 

Drag the right little square of the cell B1 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 plot.
 

Highlight the cells A1:B100.   Choose Chart, then XY(Scatter) 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:
 


 
 

Click "Finish".  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!
 

Question.  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.

Explain  briefly how this data set deviate from normality.