Place People on Bell Curve
June 19, 2018 - by Bill Jelen
Jimmy in Huntsville wants to plot a bell curve showing the average scores of several people. When Jimmy asked the question during my Power Excel Seminar, I thought back to one of my more popular videos on YouTube.
In Podcast 1665 - Create a Bell Curve in Excel, I explain that to create a bell curve, you need to calculate the mean and standard deviation. I then generate 30 points along the x-axis that span a hypothetical population of people. In that video, I generated that spanned from -3 Standard Deviations to + 3 Standard Deviations around a mean.
For example, if you have a mean of 50 and a standard deviation of 10, I would create an x-axis that ran from 70 to 130. The height of each point is calculated using =NORM.DIST(x,mean,standard deviation,False).
In the image above, the numbers in A10:A40 are essentially "fake data points". I generate 31 numbers to create a nice smooth curve. If I would have used only 7 data points, the curve would look like this:
For Jimmy's data set, the actual average scores of his employees are essentially points along an x-axis. To fit them on a bell curve, you need to figure out the height or Y-value for each employee.
Follow these steps:
