Histogram plot with normal curve overlay

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
ABCD
170.99304
241.6695
387.32772
4118.2942
5115.9505
6131.994
714.49237
872.97456
9112.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
ABCD
1BinFrequencyCumulative%
2-32.24771.03%
3-28.86830.03%
4-25.48890.03%
5-22.10951.05%
6-18.73020.05%
7-15.35080.05%
8-11.97140.05%
9-8.592061.08%
10-5.212691.10%
11-1.833327.28%
121.5460524.38%
134.9254226.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
ABCD
1BinFrequencyCumulative%
2-32.24771.00%
3-28.86830.00%
4-25.48890.00%
5-22.10951.00%
6-18.73020.01%
7-15.35080.01%
8-11.97140.01%
9-8.592061.02%
10-5.212691.02%
11-1.833327.03%
121.5460524.04%
134.9254226.06%
148.30479310.08%
1511.684168.10%
1615.0635314.13%
1718.442916.16%
1821.8222725.20%
1925.2016440.25%
2028.5810247.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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
In Excel 2007, from the Chart Tools group, choose the Design tab, and use the Select Data tab. Make any necessary changes there.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top