Microsoft Office Tutorials and References

In Depth Information

**Mathematics and Statistics Applications**

Mathematics and Statistics

Applications

The examples in this section may appeal to those involved with mathematical and

statistical applications.

Creating frequency distributions and histograms

A frequency distribution is a summary table that shows the frequency of each value

in a range. For example, an instructor may create a frequency distribution of test

scores. The table would show the count of test scores in various numeric ranges. A

chart created from a frequency distribution is often referred to as a
histogram
.

Excel provides a number of ways to create frequency distributions. You can

Use the FREQUENCY function

Use the Analysis ToolPak add-in

Use a pivot table

This section covers the FREQUENCY function and the Analysis ToolPak

options. Refer to Chapter 9 for examples of using a pivot table to create a

histogram.

USING THE FREQUENCY FUNCTION

Using Excel’s FREQUENCY function is probably the easiest way to create a

frequency distribution. This function always returns an array, so you must use it in an

array formula entered into a multicell range.

Figure 8-38 shows a workbook with data in range A2:1001 (named
Data
). These

values range from 43 to 100. The range C5:C14 contains the
bins
used for the

frequency distribution. Each cell in this bin range contains the upper limit for the bin. In

this case, the bins consist of <=55, 56–60, 61–65, and so on. See the sidebar “Creating

Bins for a Frequency Distribution” to discover an easy way to create a bin range.

To create the frequency distribution, select a range of cells that correspond to the

number of cells in the bin range — in this example, range D5:D14. Then enter the

following array formula:

=FREQUENCY(Data,C5:C14)