Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
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 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 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)+1)/5)-1}
Figure 7-7: Frequency distributions created using the FREQUENCY function.
Using formulas to create a frequency distribution
Figure 7-8 shows a worksheet that contains test scores for 50 students in column B. (The range is
named Grades. ) Formulas in columns G and H calculate a frequency distribution for letter grades.
The minimum and maximum values for each letter grade appear in columns D and E. For
example, a test score between 80 and 89 (inclusive) qualifies for a B.
 
Search JabSto ::




Custom Search