Microsoft Office Tutorials and References
In Depth Information
USING THE ANALYSIS TOOLPAK TO CREATE A FREQUENCY DISTRIBUTION
Creating Bins for a Frequency Distribution
When creating a frequency distribution, you must first enter the values into the bin
range. The number of bins determines the number of categories in the distribution.
Most of the time, each of these bins will represent an equal range of values.
To create 10 evenly spaced bins for values in a range named Data , enter the following
array formula into a range of 10 cells in a column:
=MIN(Data)+(ROW(INDIRECT(“1:10”))*(MAX(Data)-MIN(Data))/10)
To enter a multicell array formula, select the range, type the formula, and press
Ctrl+Shift+Enter.
This formula creates 10 bins, based on the values in the Data range. The upper bin will
always equal the maximum 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))/5)
Figure 8-39: The Analysis ToolPak’s
Histogram dialog box
Specify the range for your data (Input Range). If you’ve created a bin range,
specify that range — otherwise, leave it blank and the program will generate bins
automatically. Specify the upper-left cell for the results (Output Range) and then
select any options. Figure 8-40 shows a frequency distribution (and chart) created
with the Histogram option.
Search JabSto ::

Custom Search