Microsoft Office Tutorials and References
In Depth Information
USING ADJUSTABLE BINS TO CREATE A HISTOGRAM
Figure 8-40: A frequency distribution (and chart) generated by the
Analysis ToolPak’s Histogram option
A potential problem with using this technique is that the frequency
distribution consists of values, not formulas. Therefore, if you make any changes to
your input data, you need to rerun the Histogram procedure to update the
results.
USING ADJUSTABLE BINS TO CREATE A HISTOGRAM
Figure 8-41 shows a worksheet with student grades listed in column B (67 students
total). Columns D and E contain formulas that calculate the upper and lower limits
for bins, based on the entry in cell E1 (named BinSize ). For example, if BinSize is
10 (as in the figure), then each bin contains 10 scores (1–10, 11–20, and so on).
The chart uses two named formulas. The name Categories is defined as
=OFFSET(Sheet3!$E$4,0,0,ROUNDUP(100/BinSize,0))
The name Frequencies is defined as
=OFFSET(Sheet3!$F$4,0,0,ROUNDUP(100/BinSize,0))
The net effect is that the chart adjusts automatically when you change the
BinSize cell. Figure 8-42 shows the chart when the bin size is 6.
Search JabSto ::




Custom Search