Statistics Laboratory #6

The Normal Distribution

Introduction. In this lab, we will learn to do computations using Excel's normal distribution functions. These involve finding a proportion of the population given values of the random variable and the reverse problem: finding values of the random variable given the proportion of the population. We will also demonstrate the somewhat surprising result expressed in the Central Limit Theorem.

Objectives -use EXCEL to:

1. Determine the probability that an observation will be above or below a given value or between

two values. 2. Determine the value of an observation when the proportion above or below that value is given. 3. Illustrate the Central Limit Theorem and the formulas for the population mean and standard

deviation of sample means.

Procedure

Refer to the first Excel printout for assistance in completing parts 1) and 2).

1) Use the EXCEL functions NORM.S.DIST (the standard normal distribution) and NORM.S.INV to

compute a) The probability that an observation will be less than z = 0.5. b) The probability that an observation will be greater than z = 1.7. c) The probability that an observation will be between z = 0.5 and z = 1.7. d) The observation that 32% of the observations are larger than. e) The observation that 3% of the observations are less than.

Each of these requires only a single Excel statement. NORM.S.DIST(z) returns the proportion of the population that is less than z. NORM.S.INV(y) returns the value of z that has a proportion of the population y less than z. Remember: proportion to the left plus proportion to the right equals 1.

2) Apply the EXCEL normal distribution NORM.DIST and NORM.INV to solve the following

problems.

a) The average salary for firstyear teachers is $33,163. If the distribution is approximately normal

with = $3300, what is the probability that a randomly selected firstyear teacher makes:

(i) between $25,000 and $35,000 per year and (ii) less than $25,000? b) A local medical research association proposes to sponsor a footrace. The average time it takes

to run the course is 45.8 minutes with a standard deviation of 3.6 minutes. If the association decides to include only the fastest 25% of the racers, what should be the cutoff time in the tryout run? c) If the average price of a new home is $145,500, find the maximum and minimum prices of the houses a contractor will build to include the middle 80% of the market. Assume that the standard deviation of prices is $5000 and the price is normally distributed.

1 Lab 6

These 3 problems can be solved in the same manner as those in #1 with the additional input of the mean and standard deviation. For example, NORM.DIST(x, , , TRUE) returns the area to the left of x under the normal distribution whose mean is and standard deviation .

3) In this part of the lab, we will demonstrate the Central Limit Theorem (CLT). We begin by

generating samples of a discrete variable that has the binomial distribution with 4 trials and a success probability of 0.25 on each trial. This variable can take on only the whole numbers {0,1,2,3,4}. The CLT says that the means of all equalsized samples should be approximately normally distributed provided the sample sizes are sufficiently large. We'll use samples with 30 observations which should be large enough to observe the approximately normal distribution. This theory, of course, applies to the population of sample means. Since the population size of sample means is infinite, we have to be content with a large, finite number of samples. We'll use 200 samples and hope that this is enough to demonstrate the shape of the population distribution. The second Excel printout will help guide you. Note that many rows and columns were left out of this printout so that the remainder is more readable. a) On a new sheet, duplicate the labelling shown. Starting in A4, enter the numbers 1 to 200 to

designate the sample number. You can do this efficiently by first entering 1 in cell A4, 2 in cell A5, highlight the pair of cells and drag the little box at the bottom righthand corner down the column until the number 200 is indicated. b) Starting in cell B3, follow the same procedure to list the integers 1 to 30 across the columns

ending in cell AE3. These designate the observation number in each sample. c) Select Data Analysis from the DATA tab and click on Random Number Generation. Enter 30 for

the Number of Variables, 200 for the Number of Random Numbers, select the Binomial Distribution, enter the number of trials and the success probability (Excel calls it "pvalue" ) and enter B4 for the output range. Select OK. This generates a 200 X 30 array of values of a binomially distributed random variable which we will treat as our 200 samples of size 30. d) Compute the sample means by typing Mean in cell AF3 and =AVERAGE (B4:AE4) in cell AF4.

Copy this formula down the rows of the 200 samples. We now have 200 sample means and we're going to look at their frequency distribution. e) This large data array is cumbersome to have on the screen while we work so we will hide rows

and columns so we don't have to look at them. They are still there; they're just not displayed on the screen or on a printout. Highlight columns G through AA. Then from the HOME tab, select Format from the Cells group and select "Hide and Unhide" and finally Hide Columns. Follow the same procedure to hide rows 28 through 201. You should now be looking at a nice "little" array which gives a sense of the data without taking up a lot of space. f) Construct a histogram of the sample means as you learned to do in Lab #2, selecting this

function from the Data Analysis group. Select Chart Output to get a graphical display of the frequency distribution. Use the bin upper limits shown on the printout. g) State in your own words what this exercise has demonstrated. h) Compute the mean and standard deviation of the 200 sample means. Compare these to the

sample mean population parameters n / and x x = = where = 1 and = 2/ 3 are

how do u make excel sheet