Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
Range D1:D12 contains the following array formula:
This array consists of each value in the array in range C1:C12, divided into 1. For example, each cell
in the original Data range that contains a 200 has a value of 0.5 in the corresponding cell in
Summing the range D1:D12 gives the number of unique items in Data. The array formula
presented at the beginning of this section essentially creates the array that occupies D1:D12 and
sums the values.
This formula has a serious limitation: If the range contains any blank cells, it returns an error. The
following array formula solves this problem:
To create an array formula that returns a list of unique items in a range, refer to
Creating a frequency distribution
A frequency distribution basically comprises a summary table that shows the frequency of each
value in a range. For example, an instructor may create a frequency distribution of test scores.
The table would show the count of As, Bs, Cs, and so on. Excel provides a number of ways to
create frequency distributions. You can
h Use the FREQUENCY function.
h Create your own formulas.
h Use the Analysis ToolPak add-in.
h Use a pivot table.
The frequency distribution.xlsx workbook that demonstrates these four
techniques appears on the companion CD-ROM.
The FREQUENCY function
The first method that I discuss uses Excel’s FREQUENCY function. This function always returns an
array, so you must use it in an array formula entered into a multicell range.