Convert Uniform data to normal distribution

[ajhavery]

I have a data set which list the number of letters posted on 11 Mar = 12,36,345.

These letter got delivered to people over next 25 days following a normal distribution.

How can i generate a distribution which lists the number of letter delivered over 25 days - 11 March, 12 March, ..... , 3 April, 4 April.

Date	Letters Delivered
11 Mar
12 Mar
13 Mar
14 Mar
15 Mar
16 Mar
17 Mar
18 Mar
19 Mar
20 Mar
21 Mar
22 Mar
23 Mar
24 Mar
25 Mar
26 Mar
27 Mar
28 Mar
29 Mar
30 Mar
31 Mar
1 Apr
2 Apr
3 Apr
4 Apr
Total letters delivered	12,36,345

<tbody>
</tbody>

I've also asked the same question at: Convert Uniform data to normal distribution

 

[joeu2004]

I have a data set which list the number of letters posted on 11 Mar = 12,36,345. These letter got delivered to people over next 25 days following a normal distribution. How can i generate a distribution which lists the number of letter delivered over 25 days - 11 March, 12 March, ..... , 3 April, 4 April.

First, this has nothing to do with "converting" a uniform distribution to a normal distribution. Good thing, too: that did not make sense to me.

Try the following:

	A	B	C
1	n	1,236,345
2	days	25
3	norm-n	1,236,423.32		B3: =B1/(NORMSDIST(4)-NORMSDIST(-4))
4	delta-z	0.32		B4: =8/B2
5		z	n(z)
6	11-Mar	-3.68	105.03	B6: =-4+B4 C6: =$B$3*(NORMSDIST(B6)-NORMSDIST(-4))
7	12-Mar	-3.36	337.66	B7: =B6+$B$4 C7: =$B$3*(NORMSDIST(B7)-NORMSDIST(B6))
8	13-Mar	-3.04	980.70	Copy B7:C7 down through B30:C30
9	14-Mar	-2.72	2,573.25
10	15-Mar	-2.40	6,099.82
11	16-Mar	-2.08	13,063.10
12	17-Mar	-1.76	25,273.90
13	18-Mar	-1.44	44,177.15
14	19-Mar	-1.12	69,762.94
15	20-Mar	-0.80	99,530.24
16	21-Mar	-0.48	128,289.18
17	22-Mar	-0.16	149,393.12
18	23-Mar	0.16	157,172.80
19	24-Mar	0.48	149,393.12
20	25-Mar	0.80	128,289.18
21	26-Mar	1.12	99,530.24
22	27-Mar	1.44	69,762.94
23	28-Mar	1.76	44,177.15
24	29-Mar	2.08	25,273.90
25	30-Mar	2.40	13,063.10
26	31-Mar	2.72	6,099.82
27	1-Apr	3.04	2,573.25
28	2-Apr	3.36	980.70
29	3-Apr	3.68	337.66
30	4-Apr	4.00	105.03
31	TOTAL		1,236,345.00	C31: =SUM(C6:C30)

<tbody>
</tbody>

Explanation....

We want the sum under -4sd to 4sd to be n. Note that C31 does indeed equal B1.

Since the norm dist is from -inf to +inf (infinity), the "norm-n" (B3) is n/(NORMSDIST(4) - NORMSDIST(-4)).

Think of the table above as the data for a histogram with 25 buckets.

The width of each bucket (B4) is (4 - (-4))/B2 = 8/B2.

The values under the label "z" are the right edge of each bucket. The left edge of the first bucket is -4.

The values under the label "n(z)" are the amount of data in each bucket; that is, a percentage of the total (norm-n).

The expression of the form NORMSDIST(B7)-NORMSDIST(B6) is the probability (percentage of the total) of data that falls within that bucket (between two z-values).

Of course, you will want to round those values, since we cannot have a fractional number of letters per day. You are free to choose the best way to round in order to minimize quantization error and to ensure that the total is still "n" (B1).

 

[ajhavery]

Thanks! This solves my problem.

First, this has nothing to do with "converting" a uniform distribution to a normal distribution. Good thing, too: that did not make sense to me.

Try the following:

	A	B	C
1	n	1,236,345
2	days	25
3	norm-n	1,236,423.32		B3: =B1/(NORMSDIST(4)-NORMSDIST(-4))
4	delta-z	0.32		B4: =8/B2
5		z	n(z)
6	11-Mar	-3.68	105.03	B6: =-4+B4 C6: =$B$3*(NORMSDIST(B6)-NORMSDIST(-4))
7	12-Mar	-3.36	337.66	B7: =B6+$B$4 C7: =$B$3*(NORMSDIST(B7)-NORMSDIST(B6))
8	13-Mar	-3.04	980.70	Copy B7:C7 down through B30:C30
9	14-Mar	-2.72	2,573.25
10	15-Mar	-2.40	6,099.82
11	16-Mar	-2.08	13,063.10
12	17-Mar	-1.76	25,273.90
13	18-Mar	-1.44	44,177.15
14	19-Mar	-1.12	69,762.94
15	20-Mar	-0.80	99,530.24
16	21-Mar	-0.48	128,289.18
17	22-Mar	-0.16	149,393.12
18	23-Mar	0.16	157,172.80
19	24-Mar	0.48	149,393.12
20	25-Mar	0.80	128,289.18
21	26-Mar	1.12	99,530.24
22	27-Mar	1.44	69,762.94
23	28-Mar	1.76	44,177.15
24	29-Mar	2.08	25,273.90
25	30-Mar	2.40	13,063.10
26	31-Mar	2.72	6,099.82
27	1-Apr	3.04	2,573.25
28	2-Apr	3.36	980.70
29	3-Apr	3.68	337.66
30	4-Apr	4.00	105.03
31	TOTAL		1,236,345.00	C31: =SUM(C6:C30)

<tbody>
</tbody>

Explanation....

We want the sum under -4sd to 4sd to be n. Note that C31 does indeed equal B1.

Since the norm dist is from -inf to +inf (infinity), the "norm-n" (B3) is n/(NORMSDIST(4) - NORMSDIST(-4)).

Think of the table above as the data for a histogram with 25 buckets.

The width of each bucket (B4) is (4 - (-4))/B2 = 8/B2.

The values under the label "z" are the right edge of each bucket. The left edge of the first bucket is -4.

The values under the label "n(z)" are the amount of data in each bucket; that is, a percentage of the total (norm-n).

The expression of the form NORMSDIST(B7)-NORMSDIST(B6) is the probability (percentage of the total) of data that falls within that bucket (between two z-values).

Of course, you will want to round those values, since we cannot have a fractional number of letters per day. You are free to choose the best way to round in order to minimize quantization error and to ensure that the total is still "n" (B1).