Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

Range D1:D12 contains the following array formula:

{=1/C1:C12}

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

D1:D12.

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:

{=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, refer to

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

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.