Bell Curves for Teachers

[Creeze]

I need a graph showing test scores in a bell curve form. I assume it
has something to do with the count function, but can't quite figure out
a easy way to graph it.

[Earl Kiosterud]

Hi Creeze;

There are likely tools to automate this more, and perhaps someone will
post. But here's the way to do this with Excel tools.

Make a pivot table that groups your scores, and counts the groups. Post back
if you want details on that. Now you can make a chart of the scores and the
corresponding counts from the table, and you have your bell.

If you're going to use this with varying data, you should use a dynamic
range technique for both the pivot table range, and the chart's source data
range.

Regards from Virginia Beach, VA

Earl Kiosterud
ea...@livenet.net
-------------------------------------------------------------

Creeze wrote in message <38000A34...@dmi.net>...

[Tushar Mehta]

[This followup was posted to microsoft.public.excel.charting and a copy was
sent to Creeze <Cre...@dmi.net>.]

A bell curve is a particular shape of a histogram plot for a data set. There
is no guarantee that the elements in a data set will indeed be distributed in a
bell shape.

That said...

use Tools | Data Analysis... | Histogram to get a histogram for the test
scores. If you are lucky, Excel's default selection of bin ranges will give
you the shape you desire. If it doesn't, then, up to a point, it is possible
to manipulate the bin ranges so that the dispersion of the test scores takes on
the shape you desire. Experiment with the bin range values.

There is a test to verify that the scores really follow a normal distribution
(that is what a bell curve implies). I believe it involves the use of "normal
graph paper" and the resulting plot should be in a straight line. Some years
back I had figured out how to do that in Excel but can't remember right now.
Maybe some one else will help out.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <38000A34...@dmi.net>, Creeze <Cre...@dmi.net> wrote

[Paul]

One way would be to use the Histogram analysis tool. This will generate a
bar chart and if the scores are normally distributed the chart will take on
a bell curve shape. Using this tool requires that you have the Data
Analysis add-in installed. See the help files for more info or post back
if you need more help.

[David J. Braden]

Creeze:
Far easier to derive, and, IMO, interpret, is to just use the empirical
distribution (this is sometimes called the empirical cumulative distribution
function). Sort the grades. Calculate in a cell, say, B1, =1/n , where n is the
number of students.

Set the lowest grade =B1/2; I'll assume you entered this into B4 for that
student. Then, into B5 enter =B4+$B$1. Copy this down for the number of
students. Now, instead of fitting them into a Normal distribution, you can
focus on what is really going on. You can chart the ranking (in, say, column B)
against the final score, and get a lot of information. IMO, there is no reason
to expect a "bell curve" from any one class; this is certainly sound from a
theoretical standpoint, as well as my experience in grading for 11 years in
large graduate classes.

Tushar (who, btw, is a very good speaker; I hope he pursues a career involving
instruction) had a characteristically subtle point in his first paragraph. I
have stated it more firmly: drop the assumption of normality, and go with what
you have.

Tests for normality, which Tushar alluded to, we can provide, but I like his
overall spirit: really doesn't apply here, IMHO.

HTH
Dave Braden

[la...@me.com]

Sorry to chuckle ... I know what you mean, but the question sounds like
a Whisky course ... ("Bell's" and "Teacher's" are marks of Whisky in the UK)
'Sinatra'

Creeze <Cre...@dmi.net> wrote in message news:38000A34...@dmi.net...