Fog Creek Software
Discussion Board




Excel graphing question

Having trouble generating a basic graph in Excel.

I have a list of test scores. 
I'd like to make a graph of the frequency of each score.
(5 grades of 95.    3 grades of 88 , etc)

Can this be made from one column of grades?

If not, I can manually create 2 columns like:
95 | 5
88 | 3
etc

Can the graph be made from these 2 columns?

By default, Excel simply treats the 2 columns independently, and tries to graph 2 independent lines...

Help!

thanks
Thursday, April 15, 2004

Sounds like you need the Histogram tool.  On the "Tools" menu, choose "Data Anlaysis", then the "Histogram" analysis tool. The help in there is pretty good.

You will need the analysis tool pack installed to do this - it's an option in Excel setup.

This all applies to Excel 2000 - I can't speak for other versions.

Mark B
Thursday, April 15, 2004

Same in 2003,

look up "Create a histogram" in the help for a step by step explanation.

Just me (Sir to you)
Thursday, April 15, 2004

If you have or can get your data in 2-column format like:

95|3
88|7
etc.,
then it's fairly easy.
Select the second column as the data to be charted.
Fire up the chart wizard, choose a bar graph, and hit next.
On this screen, there is a 'Series' tab that gives you the opportunity to set the Category axis labels. Set them to the first column.

At this point you'll have a set of bars labelled with marks, the bar-height will indicate how many of each mark were found in the data, and the chart as a whole will give you some idea of distribution, especially if you sort by one column or the other.

At this point, you may wish to further customize the presentation and/or play around with different chart types. Don't forget to stay focused on your objectives: Charts can illuminate or obscure and the choices you make along the way will determine which kind of chart you end up with.

Ron Porter
Thursday, April 15, 2004

if you have a really old version of excel (no histogram tool) you can generate the second set (value,frequency pairs) with array functions. there was an article years ago on how to do this, it's probably online somewhere.

basically you have something which is like sum(if(range == value one column to the left)) and hit control-enter to make it an array function.

mb
Thursday, April 15, 2004

A histogram might be one way of looking at it, but getting Excel to do histograms isn't all that easy, as I recall.  A dot-plot might also be useful, but again I don't know off the top of my head if Excel will even do that.

But you can get it to do a bar chart with the grades as labels.  When Excel asks you for source data, don't give it a data range.  Instead, look for a tab or something (sorry, I'm posting this from my Mac, so the interface may not match your version, and I'm too lazy to go bring it up on the PC) that says "Series."  Create a series.  In the "Values" field, tell it where to find your frequencies.  Then, in the "Category/X-axis labels" field, tell it where to find the individual test scores (93, 88, etc).

Sam Livingston-Gray
Thursday, April 15, 2004

Getting Excel to do histograms is brain-dead easy: use the Histogram function from the Analysis Toolpack, as somebody else already said.

Martha
Friday, April 16, 2004

*  Recent Topics

*  Fog Creek Home