Excel 2019 Bible

Chapter 33: Analyzing Data with the Analysis ToolPak

3333

FIGURE 33.5

Output from the F-test tool

Histogram

The histogram tool is useful for producing data distributions and histogram charts. It
accepts an Input range and a Bin range. A Bin range is a range of values that specifies the
limits for each column of the histogram. If you omit the Bin range, Excel creates 10 equal-
interval bins for you. The size of each bin is determined by the following formula:

=(MAX(input_range)– MIN(input_range))/10

Figure 33.6 shows output from the histogram tool. As an option, you can specify that the
resulting histogram be sorted by frequency of occurrence in each bin.

If you specify the Pareto (Sorted Histogram) option, the Bin range must contain values and
can’t contain formulas. If formulas appear in the Bin range, Excel doesn’t sort properly and
your worksheet displays error values. The histogram tool doesn’t use formulas, so if you
change any of the input data, you need to repeat the histogram procedure to update the
results.

For other ways of generating frequency distributions, see Chapter 30, “Analyzing Data with PivotTables.”

Excel 2019 also supports two chart types: Histogram and Pareto. These work with a range of data and

do not require a separate bin range. For an example, see Chapter 20, “Getting Started with Excel

Char ts.”

Moving average

The moving average tool helps you smooth out a data series that has a lot of variability.
This procedure is often used in conjunction with a chart. Excel does the smoothing by com-
puting a moving average of a specified number of values. In many cases, a moving average
enables you to spot trends that otherwise would be obscured by noise in the data.