Normal distribution curve
 

How do I create a normal distribution curve in excel. I have the following
data:

In R2 TO R69 I have cost as a percentage of value for different projects. I
want to create a normal distribution curve for the values in R2 TO R69 to see
how these % line up on a normal distribution curve. I calculated the average
and stdev for these percentages and then I used bn range but my calculations
do not work out . Thanks.

The values in R2 TO R69 unsorted are and there are some blanks:
6%
79%
45%
122%

22%
120%
6%
57%
25%
17%
83%
50%
32%
12%
76%
18%
2%
64%
5%
2%
23%

62%
58%
13%
23%
35%
22%


100%
13%
79%
8%
31%
18%
65%
61%
12%

72%
85%

26%


60%
62%
63%
94%
104%
91%
91%
58%
38%
31%
86%

43%
78%

74%
16%
70%
94%
40%
5%

Normal distribution curve
 

I put your data in column A
In D2:D25 I have the values 5%, 10%, 20%...120% (since 120% is your max)
In E1, I entered the text "freq"
II selected E2:E25 and typed =FREQUENCY(A1:A68,D2:D25) which I completed
with CTRL+SHIFT+ENTER as it is an array formula

In E29 I computed the average of the A data
In E30 I computed the std dev of the A data
In G2 I typed =NORMDIST(D2,$E$29,$E$30,FALSE) and copied down to row 25
Now we need to normalize the data (have the experimental and theoretical
data sum to the same value (have same area under curve)

In E27, =SUM(E2:E25) and in F27 =SUM(F2:F26)
In F1 text "norm"
In F2 =G2*$E$27/$G$27 and copy down the column
Select E1:F25 and make column chart
Right click the second data series in chart: Chart Type and make it Line
Select the First data series and format; make the gap zero to give histogram
done

email me (get addy from my website) and I will send you a sample file)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Dave" wrote in message
...
How do I create a normal distribution curve in excel. I have the following
data:

In R2 TO R69 I have cost as a percentage of value for different projects.
I
want to create a normal distribution curve for the values in R2 TO R69 to
see
how these % line up on a normal distribution curve. I calculated the
average
and stdev for these percentages and then I used bn range but my
calculations
do not work out . Thanks.

The values in R2 TO R69 unsorted are and there are some blanks:
6%
79%
45%
122%

22%
120%
6%
57%
25%
17%
83%
50%
32%
12%
76%
18%
2%
64%
5%
2%
23%

62%
58%
13%
23%
35%
22%


100%
13%
79%
8%
31%
18%
65%
61%
12%

72%
85%

26%


60%
62%
63%
94%
104%
91%
91%
58%
38%
31%
86%

43%
78%

74%
16%
70%
94%
40%
5%

Normal distribution curve
 

Probably easiest to plug your data into a preexisting template/model:
http://www.vertex42.com/ExcelArticle...ion-Excel.html

Else, see this:
http://www.exceluser.com/explore/statsnormal.htm
http://www.tushar-mehta.com/excel/ch..._distribution/

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Dave" wrote:

How do I create a normal distribution curve in excel. I have the following
data:

In R2 TO R69 I have cost as a percentage of value for different projects. I
want to create a normal distribution curve for the values in R2 TO R69 to see
how these % line up on a normal distribution curve. I calculated the average
and stdev for these percentages and then I used bn range but my calculations
do not work out . Thanks.

The values in R2 TO R69 unsorted are and there are some blanks:
6%
79%
45%
122%

22%
120%
6%
57%
25%
17%
83%
50%
32%
12%
76%
18%
2%
64%
5%
2%
23%

62%
58%
13%
23%
35%
22%


100%
13%
79%
8%
31%
18%
65%
61%
12%

72%
85%

26%


60%
62%
63%
94%
104%
91%
91%
58%
38%
31%
86%

43%
78%

74%
16%
70%
94%
40%
5%