How do I create “bell curves” in a spreadsheet

Topic

New Reply

Does anyone know how to create a bell curve in Excel? I work with “Z” scores and would like to convert the data thus created into a graph in the form of a bell curve, so that standard deviations can be shown to students.
Bruno Terlingen

Reply | Quote

Replies

I haven’t got an example to hand,
but this site might give some ideas

http://www.tushar-mehta.com/excel/charts/normal_distribution/%5B/url%5D

Reply | Quote

Thank you sir, I shall plough through the given site to see if I can make it work for me.
Bruno Terlingen

Reply | Quote

Perhaps I am getting too old to take in the given data. It seems too complicated without “real” working data. I hereby include a sample of what I would like done – perhaps if anyone has time they could generate a graph for me so that I can do some “reverse engineering”, that way I learn better.

Bruno Terlingen

Reply | Quote

How about this?

Steve

Reply | Quote

Thank you very much. All I need is to have the heights of the students (in this example) on the Y axis as the numbers 1, 2, and 3 have limited meaning.
It certainly looks better than I have been able to achieve.

Kind regards, Bruno Terlingen

Reply | Quote

I am not quite sure what you are after then. The Bell Curve is a distribution. It is the Frequency (as a number or fraction) vs the Values (Heights or Z-Values). I thought you wanted the distribution by Z-Value (Col C). I added a new distribution chart (with Bell Curve) of Number vs Height. In addition I created a Height vs Z-value (since you wanted Height as a Y, though typically it would be the other way with Z being the dependent, Y-value) and added the predicted line from the bell curve (it is not bell shaped since the distribution is not being plotted.

I also included a chart common with statistical software that (unfortunately) excel does not do directly. MS MVP Jon Peltier describes its creation at http://peltiertech.com/Excel/Charts/ProbabilityChart.html and I used that basic setup in my example.

Perhaps one of those 4 samples is what you are after or can be adapted…
Steve

Reply | Quote

Dear Sir, I understand what you are saying, and perhaps having the ability to superimpose Bell curve (Z) onto Bell curve (Ht) would be ideal. But yes I understand that the x axis needs to remain uniform or be constant whereas the y axis is the one with the actual data. I shall now read the given URL and see if I can learn some more. Again my appreciation for your combined efforts.
Bruno Terlingen

Reply | Quote

How about the Bell Curve Overlay in the attached? I took the Bell Curve (Ht) (so the chart reads the actual data and will vary) and added text boxes indicating the Mean and ±1,2,3 std Deviations.

Steve

Reply | Quote

How shall I call you? U235 or U238 lounger? In any case well done, I did not think of “ordinary” text boxes but any clear indication will do the trick.
I have tried to replicate your spreadsheets with other data but I have to admit defeat re my knowledge base. I just don’t understand why it needs that many more tables/data from which to pluck a bell curve. I am sure that I used a program about 13 years ago that seemed to be better than the present Excel re drawing graphs (my daughter was doing psychology at the time and had to have this particular program for her studies).
I realise that I need to learn a bit more but either my brain is running out of disk space or time is not on my side.

Kind regards, Bruno.

Reply | Quote

You can just call me Steve.

I put in a lot more columns to differentiate each individual with his own color (you mentioned you wanted Cols and A and C plotted originally and A has the names…) If you add more names, more columns are needed to list their individual names…

If you just want just the histogram and the bell curve you don’t need all the columns. I added a simpler dataset and simpler overlay that may be more adaptable.

Columns E-H may not have to be modified much [E is just -4 to 4 spaced at 0.25 increments, F needs cacl avg and std dev , G needs the range (B3:B16)]

So if you add more data to B you must expand the range (the easiest way is to move the last cell and summary stats down to widen the range.

Perhaps if you detailed what you wanted to do and how you want to use it, it may be easier to help setup something for you.
Steve

Reply | Quote

Thank you Steve. I think that you have the idea: Let say that I have a maximum number of 30 student in my class. I have therefore 30 names and I will have 30 scores (test results, heights, wts, etc). First of all I inform those classes that are driven by test results what a bell curve is and what is meant by standard deviations, using on-the-spot class data – colour hair say. Then when the test results come out the students can look at THEIR position along the curve rather than concentrate on the actual score they they may have achieved. In this manner the students can determine if they need to pull their socks up.

So yes, if you can make me a template that I can populate with 30 names and 30 bits of data – that would be great. By all means lock the cells that I should not/never touch. Colour those cells that need actual imput. By all means include dummy data that I can delete, though I will save your file with a read-only attribute so I don’t inadvertently stuff things up.

Kind regards, Bruno Terlingen

Reply | Quote

How is this example. Enter in the Yellow cells they are unlocked. The sheet is protected with no password. Adjust the colors of the bars if desired.

I put in some example characters. The selection was not too random, the site I found actor’s heights tended to have the tall and the short so it has many tall characters. I leave it anyone interested to discover the actor….

Steve

Reply | Quote

Steve, what can I say? Brilliant, simple and to the point. I should have “discovered” you years ago.
Next time you are in Australia I will shout you a beer/whatever!

I don’t know what you are like with PowerPoint Presentations, I have posted a question (in that forum) and the answers are not inspiring to say the least. I have taught OL&E re spreadsheets and word processing but I have not come across this issue with PPT previously.

Again many thanks, Bruno.

Reply | Quote

I am glad I could help. I hope the setup is straightforward enough for you to understand the basics. I transposed it from the original to keep the names lined up and got rid of the lookup of the names/hts.

I seldom use powerpoint so I am not sure how I could help there. I have created files where the sound is embedded, but I didn’t do anything special as far as I know…

Steve

Reply | Quote