## Thursday, June 25, 2009

### Histograms in Excel for Mac 2008

While I do have a whole 'nother post coming up about the failings of Excel 2008, one major issue that was resolved is the analysis toolpak causing Excel 2004 to crash. "Luckily", this problem is fixed by removing VBA from Excel 2008, which means there is NO analysis toolpak. Not that it was ever great to begin with, but I guess histograms are nice. Anyway, I figured people might actually want to do histograms of their own. To that end, I created an excel file that does histograms.

Some of the stuff is a bit of kludge, but I'm sure it's fairly self-explanatory: put your data in column A, and the number of points in the appropriate cell. If you need more bins, change the number of bins and adjust the chart accordingly.

Obviously, this is intended for Excel 2008 (and 2007), but it should be backwards compatible with earlier versions, as long as you have the necessary updates to read the new file format.

EDIT: As noted by a comment, the previous file had an error in the formula that was incorrect. It is now fixed, and the total frequency should be the same as the number of data points. (Thanks, Matt!)

Labels: ,

Anonymous said...

Thank you for the Histogram spreadsheet - it is much appreciated !

August 20, 2009 at 11:09 AM
Joan said...

Agreed! So much help thank you!

October 19, 2009 at 8:17 PM
Thomas said...

Thank a lot. The frequency function was beginning to annoy me very much, and your solution became my solution :)

November 1, 2009 at 9:14 AM
usmanr said...

Thanks a lot.i love ur solution,could u please create a similar sheet for relative frequency polygon?

November 8, 2009 at 3:00 PM
Hao said...

For frequency polygon, all you need to do is just change the chart type into line. There may be other little things you need to do for formatting, but otherwise I think that should work.

November 9, 2009 at 2:43 PM
Matt said...

This is a great sheet...however, it would seem that not all the data values are counted. In your example, only 95 of the 100 total data values are included in the frequency list. Perhaps this has something to do with the fact that the bins overlap (in a histogram, the bins should be disjoint). In your sheet, it looks like if a data value is exactly on one of the overlap values, it is not counted in the frequency (I tried to see if it would work on a smaller sample). Is there any way to fix this?

February 7, 2010 at 9:54 AM
Hao said...

Hi Matt,

Thanks for catching my error - it was not in the overlap between bins, but because I did not fix the row number in the equation - so the first bin would count the number of values within the range A2:A101, but the next bin would count within the range A3:A101. The equations are constructed such that they include the lower bound but exclude the upper bound. (That's also why there is an extra bin to include the largest value that would otherwise be excluded.)

February 7, 2010 at 12:25 PM
Summer said...

Thank you so very very much!!
I am working on a maths assignment and you're spreadsheet just clarified all my data as well as allowed me to create a histogram!
Highly appreciated!! It's always a comfort to see that your workings are correct!

May 16, 2010 at 8:54 PM
Anonymous said...

THANK YOU SOO MUCHHH!

I hate Mac Excel 2008.

September 10, 2010 at 5:03 AM
Anonymous said...

Yes thank you, this was so amazingly helpful. Can't believe Excel 2008 doesn't contain built in histograms.

December 28, 2010 at 8:03 AM
Anonymous said...

Hero.

June 3, 2011 at 9:26 AM
Anonymous said...

You have my sword

September 20, 2011 at 9:36 PM
Anonymous said...

Hi. You posted this spreadsheet 2 1/2 years ago, but it is still being used ... and is still very much appreciated. Thanks!
PM

January 8, 2012 at 9:40 AM
Imogen said...

Thanks so much. This just got me out of a problem!

May 29, 2012 at 3:20 AM
Anonymous said...

Brilliant!

Much thanks for doing this!

Now, is there a way to set the bins? I.E. instead of their being labeled by the values in the data set, they should be labeled by specific intervals.

For example, I have % data from 50 states... I need the bins to be

0-10
10-20
20-30, etc.

Or, rather, as that's ambiguous at the divides, labeled

0, 10, 20, 30, 40, etc.

I did notice that you can change the number of decimals in the bin labels--e.g., to show only 1 number after the decimal point:

=FIXED(F2,1)&" - "&FIXED(G2,1)

-----------
Incidentally, the Captcha system from Google or wherever is atrocious!! Maybe you can find a different one!

September 22, 2012 at 10:54 AM
Anonymous said...

I managed to figure out how to get the bins working as I wanted.

It's really easy thanks to how you set this up. I'd recommend posting a second spreadsheet that would be whole number based rather than decimal. It could have greater utility.

My bin labels now read as

=FIXED(F2,0)&" - "&FIXED(G2,0)

I entered this data:

number of bins 10
bin width 10

Thanks again! It's great to have a decent histogram tool for use with Excel!

And I changed the chart's source data to

Y values

Sheet1!$H$2:$H$11

Category X Axis labels

Sheet1!$J$2:$J$11
bin start 0
bin end 100
-----------------

That Captcha system drives me crazy! First, you have to turn on Google Analytics, which creeps me out-- who knows what they're tracking.

Next, they are truly hard to read. The label on the page also says "Type the two words" but it's actually a number and a word--that threw me as the first couple, the number field looked as if it should be a "word".

September 22, 2012 at 11:04 AM
Anonymous said...

Thank you! So helpful!

January 7, 2013 at 10:33 AM
Tiara said...

Great!

September 2, 2013 at 12:45 AM

Subscribe to Post Comments [Atom]