
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 2column format like:
953
887
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 barheight 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 controlenter 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 dotplot 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/Xaxis labels" field, tell it where to find the individual test scores (93, 88, etc).
Sam LivingstonGray
Thursday, April 15, 2004
Getting Excel to do histograms is braindead easy: use the Histogram function from the Analysis Toolpack, as somebody else already said.
Martha
Friday, April 16, 2004
Recent Topics
Fog Creek Home
