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