Convert Uniform data to normal distribution

ajhavery

New Member
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

Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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).

Last edited:
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).

Replies
2
Views
159
Replies
4
Views
147
Replies
2
Views
83
Replies
0
Views
173
Replies
1
Views
428

1,203,071
Messages
6,053,371
Members
444,658
Latest member
lhollingsworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back