The Secret To An Analysis Tool-Pakless Histogram
One of the main skills you will need to learn and master for the General
Chemistry
laboratory class is to make a histogram, which is a common graphical
method
used to visualize data. Unlike a bar chart, a histogram can actually
project how
frequency a certain data value appears in a given sample pool, which can
be
very useful when one is analyzing data. For the most part, we can create
a
histogram using the Histogram Tool in the Data Analysis Tool-Pak, which
can be
found in your MS Office 2000/2003/2007 Installation CD. It is supposedly
the
easiest way to create a histogram, even if you do have to re-run the tool
every
time you do a new simulation. However, for those of us who either 1.
cannot find
the MS Office installation CD, or 2. are coping with the hidden menu
options that
are one of the new features of Office 2007, there is another way to make a
histogram in Excel of any version. There are two steps involved:
Step 1: Create an array of bins
For starters, we need to create a dynamic array of bins, or an array of N
evenly-spaced numbers. Consider the figure below:

In order to create a dynamic array, you have to enter the formulas below.
You
can really use any cells in your spreadsheet, depending on what your table
looks
like. You don't have to use the same cells as me.
In cell D1, I have entered my minimum value, which you can find by
looking
over your data. Likewise, in cell D2, I entered in my maximum
value.
Therefore, I entered into the following cells:
E5 =$D$1
E6 =E5+($D$2-$D$1)/10
I then copied this formula from E6 to E15. This gives me a dynamic array
of 10
bins, which is what you need for the histograms you make for this
class.
Step 2: Using Excel's FREQUENCY Formula
The figure below is a screenshot of my spreadsheet with my sample data
set. I
won't go into what exactly the FREQUENCY function is in Excel, since for
those of
you who are curious enough to do so, you can always use the Help
menu for such purposes. One thing I will point out to you though is that
you will
need to select the cells you want to have your frequencies projected (in
this
case, F6 to F15, since it matches with my bins cells) BEFORE you enter
your
FREQUENCY formula. In addition, because this FREQUENCY function is an
array
function, after you enter the formula, you will need to press
Ctrl+Shift+Enter.

FYI, the basic formula for the FREQUENCY function is:
FREQUENCY(data_array,bins_array)
First, I selected cells F6 to F15.
Second, I entered my array formula into F6:
=Frequency(A2:A28,E5:E15)
Lastly, I pressed Ctrl+Shift+Enter
Creating the Histogram Chart
To create the histogram chart, simply create a bar chart using the Chart
Wizard.
Choose the Bins column for the Labels and the Count
column as the Values.
Hope this helps, and happy graphing!