Microsoft Office Tutorials and References

In Depth Information

To create ten evenly spaced bins for values in a range named
Data,
enter the following array formula into a range

of ten cells in a column:

{=MIN(Data)+(ROW(INDIRECT(“1:10”))*(MAX(Data)-

MIN(Data)+1)/10)-1}

This formula creates ten bins, based on the values in the
Data
range. The upper bin will always equal the maxim-

um value in the range.

To create more or fewer bins, use a value other than 10 and enter the array formula into a range that contains the

same number of cells. For example, to create five bins, enter the following array formula into a five-cell vertical

range:

{=MIN(Data)+(ROW(INDIRECT(“1:5”))*(MAX(Data)-

MIN(Data)+1)/5)-1}

Figure 7-7 shows two frequency distributions — one in terms of counts, and one in terms of percentages. The

figure also shows a chart (histogram) created from the frequency distribution.