Microsoft Office Tutorials and References
In Depth Information
This formula has a serious limitation: If the range contains any blank cells, it returns an error. The following ar-
ray formula solves this problem:
{=SUM(IF(COUNTIF(Data,Data)=0,””,1/COUNTIF(Data,Data)))}
To create an array formula that returns a list of unique items in a range, see Chapter 15.
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
• Use the FREQUENCY function.
• Create your own formulas.
• Use the Analysis ToolPak add-in.
• Use a pivot table.
The frequency distribution.xlsx workbook that demonstrates these four techniques is
available at this book's website.
The FREQUENCY function
The first method that I discuss uses the FREQUENCY function. This function always returns an array, so you
must use it in an array formula entered into a multicell range.
Figure 7-6 shows some data in range A1:E25 (named Data ). These values range from 1 to 500. The range
G2:G11 contains the bins used for the frequency distribution. Each cell in this bin range contains the upper limit
for the bin. In this case, the bins consist of <=50, 51–100, 101–150, and so on. See the sidebar, “Creating bins
for a frequency distribution,” to discover an easy way to create a bin range.
To create the frequency distribution, select a range of cells that corresponds to the number of cells in the bin
range. Then enter the following array formula:
{=FREQUENCY(Data,G2:G11)}
The array formula enters the count of values in the Data range that fall into each bin. To create a frequency dis-
tribution that consists of percentages, use the following array formula:
{=FREQUENCY(Data,G2:G11)/COUNT(Data)}
Search JabSto ::




Custom Search