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.
- Tom Wellington
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.