Histogram plot with normal curve overlay

[artz]

I would like to create a histogram from experimental data with the normal distribution curve overlaying the histogram.

Ther doesn't seem to be a clear way to do this. Any suggestions?

 

[NateO]

Howdy Artz, here's the process I followed to manually crank this out:

I had 4000 normally distributed numbers in a single column in a work sheet called Source Numbers. It looked like:

Book2
	A	B	C	D
1	70.99304
2	41.6695
3	87.32772
4	118.2942
5	115.9505
6	131.994
7	14.49237
8	72.97456
9	112.8507
Source Numbers

Only a lot longer. From here, making sure my Analysis Toolpak add-in is installed, I clicked on Tools->Data Analysis... I then selected Histogram. My input range was a1:a4000. My output options were New Worksheet Ply and I checked Cumulative Percentage and Chart Output. This gives me a sheet that looked like this:

Book2
	A	B	C	D
1	Bin	Frequency	Cumulative%
2	-32.2477	1	.03%
3	-28.8683	0	.03%
4	-25.4889	0	.03%
5	-22.1095	1	.05%
6	-18.7302	0	.05%
7	-15.3508	0	.05%
8	-11.9714	0	.05%
9	-8.59206	1	.08%
10	-5.21269	1	.10%
11	-1.83332	7	.28%
12	1.546052	4	.38%
13	4.925422	6	.53%
Chart Data2

Except a lot longer.

Now, in column c, Cumulative % I changed the numbers in each cell to a formula, I used the average and standard deviation from the original data sheet, Source Numbers. Here's the first formula:<pre>
=NORMDIST(A2,AVERAGE('Source Numbers'!$A$1:$A$4000),STDEV('Source Numbers'!$A$1:$A$4000),FALSE)</pre>

So now my sheet looks like this:

Book2
	A	B	C	D
1	Bin	Frequency	Cumulative%
2	-32.2477	1	.00%
3	-28.8683	0	.00%
4	-25.4889	0	.00%
5	-22.1095	1	.00%
6	-18.7302	0	.01%
7	-15.3508	0	.01%
8	-11.9714	0	.01%
9	-8.59206	1	.02%
10	-5.21269	1	.02%
11	-1.83332	7	.03%
12	1.546052	4	.04%
13	4.925422	6	.06%
14	8.304793	10	.08%
15	11.68416	8	.10%
16	15.06353	14	.13%
17	18.4429	16	.16%
18	21.82227	25	.20%
19	25.20164	40	.25%
20	28.58102	47	.31%
Chart Data

At this point the Cumulative % curve is updated to the normal distribution curve for my data. I then left-clicked on my chart and and changed the location to a new worksheet.

I was fairly pleased with the result. A process such as this could be followed in vba as well, thought I'd start with the manual version.

Questions or concerns, please post back to this thread. Otherwise, hope this helps.

Regards,

Nate Oliver
This message was edited by NateO on 2002-08-30 15:40

 

[wellinth]

Artz,

Here's how I did this in an Excel spreadsheet:

1. Put your bins in for the histogram in a separate column.

2. Go to "Tools", "Data Analysis", "Histogram" in the pull down Excel menu.

3. Enter your data values in the Input Range box and your bin values in the Bin Range Box

4. Indicate where you want the output, preferably it should be on the same sheet. Do not choose chart output.

5. Express the "Frequency" counts as percentages by dividing the counts by the number of data points. (You can use the count function to get this.)

6. Create a x-y (Scatter Chart) of the bins by the percentages created in #5.

7. Change the chart type to column and set the gap width to zero. (Click on the data series and choose format selected data series. Look under options in the Format Data Series dialog box.)

8. Calculate the mean and standard deviation of your data set.

9. Use the Excel normdist function to calculate normal pdf values for each bin value. (Use the mean and standard deviations created in step 8 as values for the Mean and Standard_dev arguments of the normdist function).

10. Copy the normal values in step 9. Go to the chart and choose paste special as new series.

11. Click on the new series, go to chart options, chart type. Select line chart.

12. Highlight the new series again and choose format selected data series. Choose axis, plot on secondary axis.

13. Add a title to the secondary axis.

That's it. Try it. Let me know if it works.

Regards,

- Tom Wellington
email: ThomasWellington@msn.com

PS If you or anyone want an example of this, just send me e-mail and I will send you a sample spreadsheet. I'm sorry, but, I don't know how to attach one to this message.

 

[vancampc]

Hi! I used these two suggestions, and my graph is about what I'd like it to be. However, I have a question about how I can make sure that the values on the Y axes are correct such that the curve is fitting the histogram correctly. That is, I can change the max in the Y axis to make the curve go above or under various parts of the historgram, so how do I know what the correct values are such that they historgram and curve are exactly where they should be in relation to each other?
Thanks!
Carole

 

[Analyse-it]

Our add-in for Microsoft Excel, the Analyse-it Standard edition, lets you easily create a histogram with a correctly aligned normal overlay. Analyse-it also generates dot-plots and various box-plots so you can see the distribution of your data easier, and includes normality tests and a normal QQ plot if you're specifically testing for a gaussian distribution.

 

[haoyinshan]

@Vancampc,

Follow the Wellinth's procedure to step 10, everything is perfect as you want. However, you might get problem after Wellinth's step 11, the curve and the histogram might not use the same X value. If they are, you are lucky, the job is done. If they are not, follow the next:

1. Left click to choose the curve, right click and choose 'Source data', select the curve data, delete the thing in 'X Values', click OK. You will see the curve is somehow overlay on the histogram.

2. It might not be perfectly overlayed, then Left click to choose the curve, right click and choose 'Format data series', under Axis tab, select 'Primary axis'.

Now, you should have a perfect curve overlaying on the histogram.

Hope this helps you, and let me know whether it works.

Haoyin

 

[salmanshbr]

Hi,
I am using Ms Excel 2007 and it doesnot have a source data option as mentioned in step 1. Can you please advice what to do?it doesnot give any X value.....

Regards,
Salman

@Vancampc,

Follow the Wellinth's procedure to step 10, everything is perfect as you want. However, you might get problem after Wellinth's step 11, the curve and the histogram might not use the same X value. If they are, you are lucky, the job is done. If they are not, follow the next:

1. Left click to choose the curve, right click and choose 'Source data', select the curve data, delete the thing in 'X Values', click OK. You will see the curve is somehow overlay on the histogram.

2. It might not be perfectly overlayed, then Left click to choose the curve, right click and choose 'Format data series', under Axis tab, select 'Primary axis'.

Now, you should have a perfect curve overlaying on the histogram.

Hope this helps you, and let me know whether it works.

Haoyin

 

[Cindy Ellis]

In Excel 2007, from the Chart Tools group, choose the Design tab, and use the Select Data tab. Make any necessary changes there.