Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Histograms in Excel 2008

O

orbit

macrumors member
Original poster
Sep 3, 2004
39
0
Hi there,

How would I go about creating a histogram of the number of students in each block of 10% scores (eg <10, 10-20, 20-30...... 90-100)

If im given a list of marks like:
although the actual list is much longer

Code: 
30
27.5
10
55
62.5
42.5
70
92.5
61
67.5
67.5
50
87.5
60
25

its just for a homework problem, but im still finding my way around excel 2008.

Thanks
 
orbit said:
How would I go about creating a histogram of the number of students in each block of 10% scores (eg <10, 10-20, 20-30...... 90-100)
Click to expand...

Sorry to say, but Excel 2008 doesn't have support for histograms. :( You can do it with Excel 2004 with the analysis tools.

An Excel guru could do something like a histogram using the REPT function, but that's honestly above my Excel capabilities.

Regards,
Nadyne.
 
Well, its certainly not pretty but you could do it with countif statements. Set up your ranges, then use

=COUNTIF(range,"<10")

then to work around COUNTIF weakness, your next one will have to be

=COUNTIF(range,"<20")-SUM(PREVIOUSCELLS)

where SUM(PREVIOUSCELLS) is the previous counts. You will have to keep adding those, as you can't do a "<10>20". Hope that makes sense. Then you can base your histogram on that. I am sure there is a more elegant solution but that will get your answer.
 
orbit said:
Hi there,

How would I go about creating a histogram of the number of students in each block of 10% scores (eg <10, 10-20, 20-30...... 90-100)

...
Click to expand...
I didn't know that Excel 2008 can't do histograms. However, Excel 2004 sucks at them. You need user-selectable bins. AFAIK, Excel 2004 provides no way for the user to select bin sizes. If you need to do a lot of graphs, then you should find a dedicated charting application. Excel may the the industry standard for spreadsheets, but it is strictly junior varsity in the charting game.
 
Ok I have had a bit of luck now.
I have followed the histogram tutorial on this page: http://www.ncsu.edu/labwrite/res/gt/gt-bar-home.html

But when I try to graph the data it plots the range and frequency as separate series. How would I make it so that the range is along the x-axis and the frequency is what is plotted.
 
I don't have XL 2008, but I think that if you go into the data area, and change it using the Series tab instead of the Range tab it should adjust properly. But I am going from memory of a couple years ago.
 
sweet thanks, almost have it all worked out now.
Except using the tutorial I posted above I can not get the x-axis values correct.
They have to stay at 0, 10, 20 etc instead of being able to write them as 0 - 9, 10, 19 etc. which kind of sucks
 
Not sure I understand: the "bin numbers" become the x-axis, not the original numbers. Perhaps you need to change the upper limits. It matters how Excel rounds the numbers.
 
ok, that tutorial is not working out right.
Ive managed to get a copy of office 2004 for os X, anyone kind enough to tell me how to create a histogram in that?
Ive been told its something to do with the analysis tools but cant even find them
 
orbit said:
ok, that tutorial is not working out right.
Ive managed to get a copy of office 2004 for os X, anyone kind enough to tell me how to create a histogram in that?
Ive been told its something to do with the analysis tools but cant even find them
Click to expand...

The Analysis Toolpak isn't part of the standard installation of Excel 2004. Go to the Tools menu, then select Add-ins. You'll need the Office 2004 discs to do it.

As for how to create it once you've got the Analysis Toolpak installed, I stumbled across this site when I searched for "mac excel 2004 histogram": Graphing with Excel: bar graphs and histograms, which looks like it has good instructions.

Regards,
Nadyne.
 
thanks for the reply but thats the same tutorial I posted above
 
Hey, I was googling for a solution to this problem and found this thread. After trying out a bunch of different things, it seems the thing to do is to get your frequency values, and then copy and paste only the data for your bins and your frequency values. Now, select all of your bin values and format them as text. Not done yet! Stupid excel still wants to read them as numbers, and it won't let you use them as x-values unless they are recognized as text, so I just put a space in front of every number.

After that, select your data, click the stacked bar thing and it should spit out a bar graph with your bins as x-values, and your frequencies as frequencies. Now you just gotta fiddle with the spacing until you get it looking good. Now if only it were possible to make box-plots!

I have no idea how the hell excel is so popular, it really is a tedious program and completely non-intuitive.
 
Numbers 08 have a template ("Science lab") that is set up to do approximately the thing you are doing. It uses the COUNTIF function (subtraction of two countif conditions, to be exact, just as Reclusivemonkey had suggested). You can further alter this table by modifying the value to be subtracted to be an argument of, say, 1/10th of your result span (to make it 10 bins total) instead of it being a fixed value.

wankeye said:
I have no idea how the hell excel is so popular, it really is a tedious program and completely non-intuitive.
Click to expand...

Alas, there is no alternative. The same is also true for many inconveniences in our life, including, for example, traffic: that highway is jammed not because everyone enjoys using it, but because there is no alternative.

Speaking about Excel, it is probably the only product of Microsoft's provenance that is actually useable and powerful. Too bad it practically requires a geek to take advantage of all its power. Even worse, the latest iteration of Excel, the 08, is a turd when compared with that from 04.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back