Welcome Guest [Log In] [Register]
Welcome to The New Coffee Room. We hope you enjoy your visit.


You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free.


Join our community!


If you're already a member please log in to your account to access all of our features:

Username:   Password:
Add Reply
Excel powerusers -- need your help!; How to make histogram or make frequency count?
Topic Started: Oct 11 2013, 07:31 AM (100 Views)
Axtremus
Member Avatar
HOLY CARP!!!
Alright, Excel powerusers, I need your help.

Let's say I have a 100x100 table, each cell containing a real number. The numbers are distributed over some range.

My objective is to make a histogram out of this set of 10,000 numbers; preferably using my own "binning." (E.g., I may want to use <50 for one bin, from 50 to 71.2 as another bin, from 71.3 to 88.1 as another bin, etc.)

All the YouTube videos(that I've found) claiming to teach you how to make a histogram seem to assume that you have some how did a frequency count and binned the data, and then making the histogram is just a matter of making a bar chart out of that frequency table. In my case, making the frequency table is itself the tedious part that I hope to avoid doing manually.

So ... if there is a quick way (some Excel functions that I can exploit, perhaps) to build that frequency table, I'd very much appreciate learning about it.

Thanks in advance!
Offline Profile Quote Post Goto Top
 
Klaus
Member Avatar
HOLY CARP!!!
I think there is the "Analysis ToolPak" which you can add to Excel and which is exactly for histograms and that stuff.

If you want a more low-tech solution, there is the "Frequency" function.
Trifonov Fleisher Klaus Sokolov Zimmerman
Online Profile Quote Post Goto Top
 
Ballyhoo
Middle Aged Carp
I'm not an Excel power user, but according to this
http://office.microsoft.com/en-au/excel-help/create-a-histogram-HP001098364.aspx
Excel allows you to specify the bins.

If you do this sort of thing regularly, then learning R would be a worthwhile investment of your time.
Offline Profile Quote Post Goto Top
 
Axtremus
Member Avatar
HOLY CARP!!!
Great ... I have been limping along with COUNTIF(), but now I think I have also figured out FREQUENCY().

I am not 100% sure about this, but it seems I had to explicitly enable an add-in package called "Solver" for Excel to make the FREQUENCY() function available to me. (This may be specific to the Office for Mac 2011 version. See http://answers.microsoft.com/e...53-bc09-5cef06ce5f23 )

Thanks for pointing me in the right direction!
Offline Profile Quote Post Goto Top
 
« Previous Topic · The New Coffee Room · Next Topic »
Add Reply