Help with normal distribution, generating ratios for bell-shape cu

[Dream]

Hi all,
I have ratios that i got from a reference for the normal distribution, where
they can be used to generate the normal distribution....depending on the
duration...for example..please note that the sum of the ratios always equals
to 1...and that the ratios are symmetrical when the duration is odd number,,
and has 2 middle identical values when the duration if even number...

duration ratios
3 0.30 0.40 0.30
4 0.20 0.30 0.30 0.20
5 0.15 0.20 0.30 0.20 0.15
6 0.05 0.15 0.30 0.30 0.15 0.05
7 0.05 0.10 0.20 0.30 0.20 0.10 0.05
8 0.05 0.10 0.15 0.20 0.20 0.15 0.10 0.05
9 0.04 0.07 0.12 0.16 0.22 0.16 0.12 0.07 0.04
...and so forth...i have it until 24
24 0.01 0.01 0.02 0.02 0.03 0.04 0.05 0.06 0.06 0.07 0.08
0.08 0.07 0.06 0.06 0.05 0.0.5 0.04 0.03 0.02 0.02 0.01 0.01

now my questions, is i want to extend this..to durations longer than
24..such as 25, 26, all the way until...50....how can i do that in excel to
make it generate the ratios for me?

thank you in advacne

 

[Joel]

the numbers you have psted are not consistent with any distribution. check
your souce for the numbers.

I think you havve polynomial distribution. Which is representative of

a(0)x^0 + a(1)x^1 + a(2)x^2

where the coifficents arre reprresented as
N!/(R!-R)!

Normally you wll see a table like this where x = 1

1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1

You can get this table in excel by using the following formula in cell A1

=FACT(ROW())/(FACT(COLUMN()-1)*FACT(ROW()-COLUMN()+1))

Then copy the formula to

A2 and B2
A3, B3, and C3
A4, B4, C4, and D4

Now instead of using x = 1 use x = .5. Put the following in cell A1 and
copy to the same cells as above

=(0.5^ROW())*FACT(ROW())/(FACT(COLUMN()-1)*FACT(ROW()-COLUMN()+1))

You can change x to any value you want.

 

[Dream]

Thank you Joel for your explanation.

I did what you suggested using x=1..then becuase i wanted the total equals
to 1, i normalized each row by dividing it by the sum of each row...i graphed
what i got against what the numbers i had earlier...i found that the
polynomail graph is very concentrated in the middle...while the one from teh
original data is more speard like....

could you please advise if there is any other method in excel that can
replicate the methodology which is present in the original numbers...using
normal distribution or any other method you suggest...i am trying to follow
the same way but apply it to longer periods...since the goal is to distribute
costs of projects across their periods...

I greatly appreciate your help

 

[Joel]

I tight spread is usually consider good, not bad. Sigma which is the square
root of the standard deviation is the width of the normal distibution curve.
A narrow curve is when the standard dvviation is also small. There is 3
sigma, 5 sigma, 6 sigma. 6 sigma has smaller deviation and a narrow curve
than 3 sigma. From a manuafacturing perspective better reliability is
consider when you have less deviation in your processes.

The general form of the equation is

P(s) = N!/(R!*(N-R)!) * F(n)

F(n) would be your normal distribution curve and N(**2) is the sum of all
the coiefficents.

1+ 4 + 6 + 4 + 1 = 16 = 4(**2)


I think you want to manually adjust the axis of your graph to get better
resolution. Clcik on the x-axis fo the graph, then double click to bring up
the format axis GUI. Then manually change the Max and Min values of the axis
to spread the graph.

 

[Joel]

To get your coieficcients always to add up to 1 Yo need to use this formula
in the 1st cell. Then copy the formula to the other cells.

=(1/(2^(ROW()-1)))*FACT(ROW()-1)/(FACT(COLUMN()-1)*FACT(ROW()-COLUMN()))


This is equivalent to

P(s) = (1/2(^N)) * N!/(R!*(N-R)!) * F(n)

 

[Herbert Seidenberg]

Excel 2007 Tables
Normal distribution
http://www.mediafire.com/file/wzj0gxwyzcw/09_08_09.xlsx