# excel 2010 – statistics

CHAPTER 6: NORMAL DISTRIBUTIONS

GRAPHS OF NORMAL PROBABILITY DISTRIBUTIONS (SECTION 6.1 OF
UNDERSTANDABLE STATISTICS)
A normal distribution is a continuous probability distribution governed by the parameters

Î¼ (the mean) and Ïƒ (the standard deviation), as discussed in Section 6.1 of Understandable Statistics
. The Excel command that generates values for a normal distribution is

NORM.DIST(x,mean,standard deviation,cumulative
)
When we set the value of cumulative to FALSE, the command gives the values of the normal probability density function for the corresponding x value. You can type the command directly into the formula bar, or find it by using the Paste Function button on the standard toolbar, selecting Statistical in the drop box and scrolling to NORMDIST in the dialog box below. We filled in the entries that we will use in the next example.

Example

Graph the normal distribution with mean

Î¼=10 and standard deviation Ïƒ
=2.
Since most of the normal curve occurs over the values

Î¼-3Ïƒ, Î¼ +3Ïƒ
,we will start the graph at 10 – 3(2) = 4 and end it at 10 + 3(2) = 16. We will let Excel set the scale on the vertical axis automatically.

Generate the column of x values

To graph a normal distribution, we must have a column of x values and a column of corresponding y values. We begin by generating a column of x values ranging 4 to 16, with an increment of 0.25. To do this, select Cell A2 and enter the number 4. Select Cell A2 again and use the

>Home>Fill>Series to open the dialog box shown next. Set the options in the dialog box as shown and then press OK.
46

You will see that Column A now contains the numbers 4, 4.25, 4.50, â€¦ all the way up to 16.

Generate the column of y values

Select Cell B2, and enter

=NORM.DIST(A2,10,2,False)

Press Enter. Select Cell B2 again and move the cursor to the lower right corner of the cell. When the cursor changes to a small black +, hold down the left mouse button and drag down the column until each Column-A entry has a corresponding Column-B entry. Release the left mouse button. All the y values should now appear.

47

Create the graph of a normal distribution

We will use a Line Graph to create the graph of a normal distribution. Select the data in column B. On the Insert Ribbon, select Line Graph and choose the first chart sub-type.

Right-click on the chart area and choose Select Data. Under Horizontal (Category) Axis Labels, click Edit. Select the cells in Column A, starting with Cell A2. Click OK twice.

48

Next, add a title and variable label to the x-axis. In the Chart Title, type

Normal Distribution, Mean = 10, St Dev=2

For Category (X) axis, type x.

Now your worksheet will contain the graph of the described normal distribution. Move the graph and size it to your liking. Notice that as you make the graph wider or taller, the labels shown on the x-axis might change.

We can also graph two (or more) normal distributions on the same graph. In the next display we generated y values for a normal distribution with mean 10 and standard deviation 1 in Column C. Then, we select both Column B and C before selecting the chart type.

49

STANDARD SCORES AND NORMAL PROBABILITIES

Excel has several built-in functions relating to normal distributions.

STANDARDIZE(x,mean,standard deviation)
returns the z score for the given x value from a distribution with the specified mean and standard deviation.
NORM.DIST(x1,mean,standard deviation,cumulative);
when cumulative is TRUE, this returns probability that a random value x selected from this distribution is â‰¤ x1, i.e. it returns P(x â‰¤ x1). This is the same as the area to the left of the specified x1 value under the described normal distribution. When cumulative is FALSE, it returns the height of the normal probability density function evaluated atx1. We used this function to graph a normal distribution.
NORM.INV(probability,mean,standard deviation)
returns the inverse of the normal cumulative distribution. In other words, when a probability is entered, the command returns the value from the normal distribution with specified mean and standard deviation so that the area to the left of that value is equal to the designated probability.
NORM.S.DIST(z
1) returns the probability that a randomly selected z score is less or equal the specified value of z1, i.e. it returns P(z â‰¤ z1). This is the same as the area to the left of the specified z1 value under the standard normal distribution. This command is equivalent to NORM.DIST(x1,0,1,true).
NORM.S.INV(probability)
returns the value such that the area to its left under the standard normal distribution is equal to the specified probability. This command is equivalent to NORM.INV(probability,0,1).
Each of these commands can be typed directly into the formula bar for an active cell or accessed by using the Paste Function button.

50

Examples

(a) Consider a normal distribution with mean 100 and standard deviation 15. Find the

z score corresponding to x
= 90 and find the area to the left of 90 under the distribution.
First we place some headers and labels on the worksheet. Then,

1. in Cell B3, enter =STANDARDIZE(90,100,15) 2. in Cell C3, enter =NORMDIST(90,100,15,true)

(b) Find the z score so that 10% of the area under the standard normal distribution is to the left of z.

Again, we put some labels on the worksheet. Then, since we are working with a standard normal

distribution, we use NORMSINV(0.1).

51

To find areas under normal curves between two values, we do simple arithmetic with the cumulative areas provided by Excel. For instance, to find the area under a standard normal distribution between -2 and 3, we would use NORM.DIST(3, 0, 1, true) to find the cumulative area to the left of 3 and then subtract the cumulative area to the left of -2, found using NORM.DIST(-2, 0, 1, true).

52

To find areas under normal curves to the right of a specified value, we subtract the cumulative area to the left of the value from 1. For instance, consider the normal distribution with mean 50 and standard deviation 5. Below is a worksheet in which the area to the right of 60 is found.

LAB ACTIVITIES FOR NORMAL DISTRIBUTIONS

1. (a) Use Excel to sketch a graph of the standard normal distribution with a mean of 0 and a standard deviation of 1. Generate x values in column A ranging from -3 to 3 in increments of 0.5. Use NORM.DIST to generate the y values in column B.

(b) Use Excel to sketch a graph of a normal distribution with a mean of 10 and a standard deviation of 1. Generate x values in Column A ranging from 7 to 13 in increments of 0.5. Use NORM.DIST to generate the y values in column B. Compare the graphs of parts (a) and (b). Do the height and spread of the graphs appear to be the same? What is different? Why would you expect this difference.

(c) Sketch a graph of a normal distribution with a mean of 0 and a standard deviation of 2. Generate x values in Column A ranging from -6 to 6 in increments of 0.5. Use NORM.DIST to generate the y values in Column B. Compare that graph to that of part (a). Do the height and spread of the graphs appear to be the same? What is different? Why would you expect this difference? Note, to really compare the graphs, it is best to graph them using the same scales. Redo the graph of part (a) using x from -6 to 6. Then redo the graph in this part using the same x values as in part (a) and y values ranging from 0 to the high value in part (a).

2. Use NORM.DIST or NORM.S.DIST plus arithmetic to find the specified area.

(a) Find the area to the left of 2 on a standard normal distribution.

(b) Find the area to the left of -1 on a standard normal distribution.

(c) Find the area between -1 and 2 on a standard normal distribution.

(d) Find the area to the right of 2 on a standard normal distribution.

(e) Find the area to the left of 40 on a normal distribution with

Î¼ = 50 and Ïƒ
= 8.
(f) Find the area to the left of 55 on a normal distribution with

Î¼ = 50 and Ïƒ
= 8.
(g) Find the area between 40 and 55 on a normal distribution with

Î¼ = 50 and Ïƒ = 8.

h) Find the area to the right of 55 on a normal distribution with
Î¼ = 50 and Ïƒ
= 8.

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount! Use Discount Code “Newclient” for a 15% Discount!NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.

## Order a similar paper and get 15% discount on your first order with us 98% Success Rate
“Hello, I deliver nursing papers on time following instructions from the client. My primary goal is customer satisfaction. Welcome for plagiarism free papers” Stern Frea
98% Success Rate
Hi! I am an English Language and Literature graduate; I have written many academic essays, including argumentative essays, research papers, and literary analysis. Dr. Ishid Elsa
98% Success Rate
"Hi, count on me to deliver quality papers that meet your expectations. I write well researched papers in the fields of nursing and medicine". Dr. Paul P. Klug
99% Success Rate
"A top writer with proven reliability and experience. I have a 99% success rate, overall rating of 10. Hire me for quality custom written nursing papers. Thank you"

### How Our Essay Writing Service Works

Fill out order details and instructions, then upload any files or additional materials if needed. Then, confirm your order by clicking “Place an Order.”

Your payment is processed by a secure system. We accept Mastercard, Visa, Amex, and Discover. We don’t share any informati.on with third parties

## The Writing Process

You can communicate with your writer. Clarify or track order with our customer support team. Upload all the necessary files for the writer to use. ## Recent Questions

### 6.4 Assignment: Final Capstone Project Paper (Phase 7 of Final Capstone Project)

6.4 Assignment: Final Capstone Project Paper (Phase 7 of Final Capstone Project)

### 6.3 Assignment: Final PowerPoint Presentation (Phase 6 of Final Capstone Project)

6.3 Assignment: Final PowerPoint Presentation (Phase 6 of Final Capstone Project)

### 6.2 Discussion: Capstone Questions

6.2 Discussion: Capstone Questions

### 6.1 Assignment: Devotional

6.1 Assignment: Devotional

### 5.4 Assignment: Draft Paper (Phase 5 of Final Capstone Project)

5.4 Assignment: Draft Paper (Phase 5 of Final Capstone Project)