Histograms in Excel 2008

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.

 

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.

 

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.

 

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.

 

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.

 

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.

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 can also just download the template on http://www.excelhistograms.com

I hated having to rework the excel formulas to create the bins every time i wanted to create new histograms.

The template works like a charm

 

This should do the trick

Here's a free template (the last poster's link costs $10)

http://collinear.blogspot.com/2009/06/histograms-in-excel-for-mac-2008.html