Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shape cu
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shape cu
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shap
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shap
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. "Dream" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shap
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) "Joel" wrote: 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. "Dream" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with normal distribution, generating ratios for bell-shapecu
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Bell Shape | Excel Worksheet Functions | |||
How to plot a normal distribution - Bell curve | Excel Discussion (Misc queries) | |||
How do I create and/or edit a bell shape graph? | Charts and Charting in Excel | |||
Bell Shape Graphs | Excel Discussion (Misc queries) | |||
bell-shape normal distribution curve | Charts and Charting in Excel |