How To Create A Bell Curve In Excel 2003

Bernard wrote:

But the data you show does not have a normal distribution - see screenshot

The screenshot does not demonstrate that at all.

You graphed the data in the order that it was generated . By your reasoning, a series of 200 formulas of the form =NORMSINV(RAND()) does not generate a normal distribution because a chart of the data in the order they are generated would look like the following:

Of course, that is not what we mean by a normal distribution at all. Instead, using yashishdookhit's data, we look at the histogram of the frequency distribution of the data like the following (explained below):

Bernard wrote:

Lots of info on bell curves if you search Excel normal distribution curve . This is a very helpful one;
https://www.techwalla.com/articles/how-to-use-excel-to-create-a-bell-curve

In the chart above, the orange curve ("norm2") is generated based on those instructions. It is not representative of the data at all, IMHO.

Instead, I believe the red curve ("norm1") is the correct expected normal distribution with the mean and standard deviation ("sd") of the actual data. It demonstrates that the actual data is indeed nearly normally distributed, IMHO.

The formulas for the image above are:

D3: =COUNT(data)

D4: =AVERAGE(data)

D5: =STDEV(data)

D6: =MIN(data)

D7: =MAX(data)

D8: =D3/(NORMSDIST(4)-NORMSDIST(-4))

D12: =D11+8/15 (copy down through D26)

E11: =$D$4+$D$5*D11 (copy down through E26)

F6: =(D6-$D$4)/$D$5

F7: =(D7-$D$4)/$D$5

G12: =$D$8*(NORMSDIST(D12)-NORMSDIST(D11)) (copy down through G26)

G27: =SUM(G12:G26)

H11: =NORMDIST(E11,$D$4,$D$5,0) (copy down through H26)

H27: =SUM(H11:H26)

F12:F26: =FREQUENCY(data,E12:E26) (array-entered; press ctrl+shift+Enter, not just Enter)

Note that the number of data (G27) in the area under the "norm1" curve between z=-4 and z=4 is 204, the same as the number of actual data (D3). Since the z-limits of a normal distribution are infinite, I use what I call "n norm" (D8) to generate the expected normal distribution in G12:G26.

The formulas in G12:G26 ("norm1") calculate the percentage of the ("n norm") data that we expect between two adjacent z-scores in a (continuous) normal distribution. That mimics the way that we count the actual data in the frequency distribution.

In contrast, the formulas in H11:H26 ("norm2") calculate number of data that we expect for a particular z-score in a (discrete) normal distribution. Even a graph of all (infinite) such statistics does not mimic the way that we represent a frequency distribution of the actual data.

Based on the statistics in columns D:G, I do the following to generate the chart in Excel 2007:

1. Select F12:F26 and insert a Column chart.

2. Right-click the chart, click Select Data, edit Horizontal Axis Labels, and select E12:E26.

3. In Select Data Source, click Add, and select G12:G26. Click OK to exit Select Data Source.

4. Right-click the "norm1" Histogram, click Change Series Chart Type, and select XY Scatter With Smooth Lines.

How To Create A Bell Curve In Excel 2003

Source: https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-create-a-bell-curve-of-my-data-for-it-to/ffc41193-1d39-443f-8446-b304469b0654

Posted by: hendricksonfalmyst64.blogspot.com