Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Frequency Distributions
This is very important: the Frequency function is an array function. When using
an array function in Excel, you are going to get the answer in more than one cell,
cells G2:G8 in our case. This corresponds to the set of bins.
A simple OK or Return cursor will result in a single cell reply (in cell G2). Since
we want to know the frequency of each of our selected bins, the answers should be
filled in the entire range (G2:G8). When the answer appears in a single cell, you
cannot drag it down or copy it to the rest of the cells. Dragging does not work in an
array function.
To get the results in all cells you have to: First make sure that all the cells that
you want the answer in are selected before entering the Frequency function, and after
filling in the different fields, hold down the CTRL and Shift keys simultaneously
and while they are pressed, click the OK button or hit Return. This will calculate
the array result in the range G2:G8. It may take some practice. See the results in
Figure 11.4.
Note the function in the formula bar in Figure 11.4. The array results cannot be
changed. If you visit any of the calculated frequency cells in column G, you will see
how the result shows curled brackets, {
FREQUENCY(DATA, F2:F8)}. Curled
brackets indicate that results of these functions cannot be altered.
A simple observation of the results: most of the data is distributed around
the average (911.21 was the previously calculated average) or the center of the
distribution. Having the frequency distribution, we can now create a chart of
the frequency distribution using the information in the range F2:G8.
In creating a frequency distribution chart, I want to have a two-dimensional
column chart. I want the bin values in column F (70, 80, . . . and 130) on the X-axis
and the frequency values of column G as Y/columns. If you don
¼
t have a header
for the X values (in column F), Excel will create the chart where these values will be
on the X-axis:
Remove the header (BINS).
n
Select the range: CTRL
þ
Shift
þ
*.
n
Use Insert 2-D Column Chart as shown in Figure 11.5.
n
FIGURE 11.4 The Frequency Function in Excel
Search JabSto ::




Custom Search