Normal distribution over time

[GeoTrek]

Hi all,

I am attempting to create a normal distribution. This is for the number of buildings built over time. Time span is 10 years.

Say I have 26 buildings planned and want them built over a 10 year period following a normal distribution (few built year one, most built year 5, few built final year). Currently it seems I am only making a bell curve for a dataset of 1-26. How can I create a curve telling me how many should be built year to year following a normal distribution?

Also, an explanation of "cumulative" variable in the NORMDIST formula may help.

Thanks!

[MrShorty]

You are correct, it does look like you are building a normal curve based on the dataset consisting of integers from 1 to 26. You are:
computing the average and standard deviation of the numbers from 1 to 26
then using those two numbers to generate a normal distribution with average of 13.5 and standard deviation of 7.3

Recognizing that the "peak" of a bell curve will be at the value you give for the average and the width of the bell curve is controlled by the standard deviation, I entered different values for average and standard deviation in B43 and B44. I put 5 in B43 (since that's where you say you want the peak) and 1 in B44. Then, to see what that means in terms of the 26 buildings you want to build over the 10 years, I added a column =B14*26. This gave me a forecast of building almost nothing until year 3. The peak of building would be years 4, 5, and 6. and almost nothing built after year 8.

Is that close to what you are looking for?

[GeoTrek]

Your comments helped me think of a different way. I tried using years as my x axis and it is closer.

Then I multiplied my y by 26 and it produced something very close, but when I sum my buildings, it only equals 23...so does not seem quite right.

I guess to clarify further, I am trying to show years on x and building built per year on y, assuming it will follow a standard bell curve. Any additional advice appreciated.

Screenshot (97).png

[MrShorty]

Doing it this way (with average and standard deviation calculated from the list of years) is not going to add up to 26. To get 26 out of this procedure, you will ned a narrower distribution, something with a smaller standard deviation (Something less than 2) will get closer to 26. I can understand why you are taking the average from the list of years -- you want the top of the bell curve to be in the center of the defined time frame. I don't understand why you are using the standard deviation of the time frame to get the width of the bell curve. You control the shape of a bell curve with the standard deviation parameter. Unless there is some other constraint causing you to use SD like this, I would adjust the SD parameter until you get something that you feel is more correct.

[shg]

One way:

	A	B	C	D	E	F
1	Years	z max		Houses
2	10	2		26
3	Year	z score	CDF	Houses
4	1	(2.000)	0.023	1.0		B4: =$B$2 * (2 * (A4 - 1) / ($A$2 - 1) - 1)
5	2	(1.556)	0.060	1.0		C4: =NORM.S.DIST(B4, TRUE)
6	3	(1.111)	0.133	2.0		D4: =ROUND($D$2 * C4 / $C$14, 0) - SUM(D$3:D3)
7	4	(0.667)	0.252	3.0
8	5	(0.222)	0.412	4.0
9	6	0.222	0.588	5.0
10	7	0.667	0.748	4.0
11	8	1.111	0.867	3.0
12	9	1.556	0.940	2.0
13	10	2.000	0.977	1.0
14		Max/Total	0.977	26.0

B2 sets the z score for the first and last years.

EDIT: That has a bug ...

[shg]

Fixed:

	B	C	D	E	F	G
2	Years	z max	Tail	Houses
3	10	2	0.023	26		D3: =NORM.S.DIST(-C3, TRUE)
4	Year	z score	CDF	Houses
5	0	(2.000)	0.000			C5: =IF(B5 > $B$3, "", $C$3 * (2 * B5 / $B$3 - 1))
6	1	(1.600)	0.034	1		D5: =IF(B5 > $B$3, "", (NORM.S.DIST(C5, TRUE) - $D$3) / (1 - 2*$D$3))
7	2	(1.200)	0.097	2		E6: =IF(B6 > $B$3, "", ROUND($E$3 * D6, 0) - SUM(E$4:E5))
8	3	(0.800)	0.198	2
9	4	(0.400)	0.337	4
10	5	0.000	0.500	4
11	6	0.400	0.663	4
12	7	0.800	0.802	4
13	8	1.200	0.903	2
14	9	1.600	0.966	2
15	10	2.000	1.000	1
16	11
17	12
18	13
19	14
20	15
21	16
22	17
23	18
24	19
25	20
26			Total	26