I was given a spreadsheet that provides the bell shaped allocations based on a number of periods. For example, if the number of periods is 8 then the distribution for each period is 5.00% 10.00% 15.00% 20.00% 20.00% 15.00% 10.00% 5.00%. if the number of periods is twelve then the distribution by period is 3.00% 5.00% 7.00% 10.00% 12.00% 13.00% 13.00% 12.00% 10.00% 7.00% 5.00% 3.00%. The contents of each cell is simply the numbers shown above as opposed to a formula that calculates each. I was wondering if someone might be able to tell me how to calculate these numbers in Excel. Thank you in advance for your cooperation.
Bell Shaped Curve
- Thread starter turoksj
- Start date
Turoksj
Firstly, Welcome to the Chandoo.org forums.
[pre]
[/pre]
The Equation I used for the Normal Curve is
=100*NORM.DIST(B3,0.5,0.221537627,TRUE)
Where 0.08333 is in B3
Mean = 0.5
SD = 0.221537627 (Adjusted to get 3%)
Firstly, Welcome to the Chandoo.org forums.
[pre]
Code:
8 Steps Normal Your
Curve Data
0.125 5.0 5.0
0.25 13.6 15.0
0.375 29.2 30.0
0.50 50.0 50.0
0.625 70.8 70.0
0.75 86.4 85.0
0.875 95.0 95.0
1.00 98.6 100.0
The Equation I used for the Normal Curve is
=100*NORM.DIST(B3,0.5,0.22798,TRUE)
Where 0.125 is in B3
Mean = 0.5
SD = 0.22798 (Adjusted to get 5%)
12 Steps Normal Your data
0 1.2 0.0
0.08333 3.0 3.0
0.16667 6.6 8.0
0.25 13.0 15.0
0.33333 22.6 25.0
0.41667 35.3 37.0
0.5 50.0 50.0
0.58333 64.7 63.0
0.66667 77.4 75.0
0.75 87.0 85.0
0.83333 93.4 92.0
0.91667 97.0 97.0
1 98.8 100.0The Equation I used for the Normal Curve is
=100*NORM.DIST(B3,0.5,0.221537627,TRUE)
Where 0.08333 is in B3
Mean = 0.5
SD = 0.221537627 (Adjusted to get 3%)
Thank you Hui for your response. Would you mind explaining your comment "adjusted to get 3%" and "adjusted to get 5%" as it relates to the standard deviation? Also, I'm not sure I understand how the data you generated is associated with the data I provided. For example if there were 15 steps how would you generate the "your data" column. Thanks again.
Turoksj
The problem here is in the definition of what the numbers mean
Using a simple example with 4 periods you have values of 0.25,0.5,0.75 and 1
This can be modeled in a Normal (Bell) curve using a mean of 0.5
But what is the standard deviation (SD)?
typically people will use either 2 SD's in which 68% of the population will fall, 3 SD's in which 95% of the population will fall or 4 SD's in which 99.7% of the population will fall to define the range around the mean.
So If I use a variable to calculate the SD's we have a table that looks like:
[pre]
[/pre]
So the Shape of the Normal Curve which is defined by the SD is important.
Your original data showed two different curves
for n=8 you need to have a SD = 0.22798 to get the value of 5% for 0.125
for n=12 you need to have a SD = 0.22798 to get the value of 3% for 0.08333
Unfortunately your data doesn't provide any measure of the SD and so the above approximations have been made to make the curves fit.
Put simply a Bell curve is an open ended curve and it is up to us to define what % of data that should fit within a given spread around the mean.
I Hope that helps explain the issue a little bit.
The problem here is in the definition of what the numbers mean
Using a simple example with 4 periods you have values of 0.25,0.5,0.75 and 1
This can be modeled in a Normal (Bell) curve using a mean of 0.5
But what is the standard deviation (SD)?
typically people will use either 2 SD's in which 68% of the population will fall, 3 SD's in which 95% of the population will fall or 4 SD's in which 99.7% of the population will fall to define the range around the mean.
So If I use a variable to calculate the SD's we have a table that looks like:
[pre]
Code:
SD= x % < x
2 SD's 0.5/2 0.125 6.68%
3 SD's 0.5/3 0.125 1.22%
4 SD's 0.5/4 0.125 0.13%So the Shape of the Normal Curve which is defined by the SD is important.
Your original data showed two different curves
for n=8 you need to have a SD = 0.22798 to get the value of 5% for 0.125
for n=12 you need to have a SD = 0.22798 to get the value of 3% for 0.08333
Unfortunately your data doesn't provide any measure of the SD and so the above approximations have been made to make the curves fit.
Put simply a Bell curve is an open ended curve and it is up to us to define what % of data that should fit within a given spread around the mean.
I Hope that helps explain the issue a little bit.
Thank you Hui for going above and beyond to answer my questions. Hope you don't mind if I ask just one more question. How would i generate the "your data" column with the information you have provided if I said to you that there were 15 steps instead of 8 or 12? To put this in context, the allocations (the "your data" column) are being used to allocate construction costs in a project finance model over the life of the project. So if the project is eight periods the expense are allocated based on the numbers provided in the first example. I'm wondering how those allocations are generated for a given number of periods. Sorry to bother you again. Really appreciate the feedback.