Let's say I have a list of values and I have already chunked them into groups to make a histogram. Since Excel doesn't have histograms, I made a bar plot using the groups I developed. Specifically, I have the frequencies 2 6 12 10 2 and it produces the bar plot you see below. Next, I want to add a normal distribution (line plot) with a mean of 0.136 and standard deviation of 0.497 on top of this histogram. How can I do this in excel?
- Graphing in excel on the Mac. Quick Reference for people who just need a reminder. The easiest thing is to have a single series, with y data in the column to the.
- Make a Histogram in Microsoft Excel. Office 2011 for Mac All-in-One For Dummies. The instructions on this page are for Microsoft Excel 2011. Instructions for Excel 2008 can be found on pages 448 through 455 of Office 2008 All-In-One for Dummies. A histogram displays the values of a frequency in a proportional graph. You're going to need.
Sep 22, 2017 - The following table shows which Excel for Mac versions include these. Yes, starting with Excel for Mac 2011, Service Pack 1 (version 14.1.0).
I need the axis to line up such that it takes up the width of the bar plot. Otherwise, you get something like I've attached.
But.the normal should be overlayed on the bar plot. How can I get this effect? There are two main part to this answer: First, I reverse-engineered the grouped data to come up with an appropriate mean and standard deviation on this scale. Second, I employed some chart trickery to make the normal distribution curve look right when superimposed on the column chart. I used Excel 2007 for this; hopefully you have the same options available in your version. Part 1: Reverse-Engineer The column B formulae are: Last Point =MAX(A2:A6) Mean =SUMPRODUCT(B2:B6,A2:A6)/SUM(B2:B6) E(x^2f) =SUMPRODUCT(A2:A6^2,B2:B6) E(xf)^2 =SUMPRODUCT(A2:A6,B2:B6)^2 E(f) =SUM(B2:B6) Variance =B10-B11/B12 StDev =SQRT(B13/(B12-1)) Part 2: Chart Trickery Data table: Column D is just an incremental counter.
This will be the number of data points in the normal distribution curve. E2 =D2/$B$8 etc. F2 =NORMDIST(E2,$B$9,$B$14,FALSE) etc. Chart: Now, add Columns E:F to the chart. You will need to massage a few things:. Change the series to be an X-Y plot.
This might require some editing of the chart series to force a single series to use your desired X and Y values. Change the series to use the secondary axes (both X and Y). Change the secondary X-axis range to 0.5-5.5 (i.e., 0.5 on either side of the column chart category values). This will effectively align the primary and secondary X-axes. Change the secondary Y-axis range to 0-1. Format the X-Y series appearance to taste (I suggest removing value markers).
Creating A Histogram In Excel
The result so far: Lastly, you can remove the tick marks and labels on the secondary axes to clean up the look. Postscript: Thanks to for innumerable charting inspirations over the years.