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.
Link Here
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!)
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.
Link Here
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: math, things that annoy me
18 Comments:
Thank you for the Histogram spreadsheet - it is much appreciated !
Agreed! So much help thank you!
Thank a lot. The frequency function was beginning to annoy me very much, and your solution became my solution :)
Thanks a lot.i love ur solution,could u please create a similar sheet for relative frequency polygon?
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.
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?
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.)
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!
THANK YOU SOO MUCHHH!
I hate Mac Excel 2008.
Yes thank you, this was so amazingly helpful. Can't believe Excel 2008 doesn't contain built in histograms.
Hero.
You have my sword
Hi. You posted this spreadsheet 2 1/2 years ago, but it is still being used ... and is still very much appreciated. Thanks!
PM
Thanks so much. This just got me out of a problem!
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!
A follow up.
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".
Thank you! So helpful!
Great!
Post a Comment
Subscribe to Post Comments [Atom]
<< Home