Microsoft Office Tutorials and References

In Depth Information

**Creating histograms**

Note

We created our own distribution bins for this model, but you can let the Histogram

tool determine the divisions for you. Leave the Bin Range box blank to create evenly

distributed bin intervals with the minimum and maximum values in the input range as

beginning and ending points. The number of intervals is equal to the square root of the

number of input values.

Here are a few facts to keep in mind when using the Histogram tool:

In the Frequency column, the histogram reports the number of input values that are

equal to or greater than the bin value but less than the next bin value.

●

The last value in the table reports the number of input values equal to or greater

than the last bin value.

●

Select the Pareto check box in the Histogram dialog box to sort the output in

descending order.

●

Select the Cumulative Percentage option to add a column showing the percentage of

total input values accounted for at each bin level, as shown in Figure 17-10.

●

If you select the Chart Output option in the Histogram dialog box, the Histogram

tool simultaneously generates a chart and places it on the worksheet.

●

For everything you need to know about charts, see Part 6, “Creating charts.”

INSIDE OUT
Beware of bin formulas

Notice that the Histogram tool duplicates your column of bin values in its Bin column,

which is convenient if you place the output somewhere else in your workbook. But

because the Histogram tool copies the bin values, it’s best if the bin range contains

numeric constants rather than formulas. If you do use formulas, be sure they don’t

include relative references; otherwise, when the Histogram tool copies the range, the

formulas might produce unwanted results.

Analyzing distribution with the FREQUENCY function

It’s easy to use the Histogram tool to generate a new frequency distribution table whenever

you change the input values, but the Histogram tool generates static numbers (numeric