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.