Part II: Working with Formulas and Functions
column F. Column E does not affect the formula; it’s just there to show the lower bound of
each bin.
To enter FREQUENCY into column G, first select the range G3:G12 and then type the for-
mula. While you’ll only be entering the formula into G3, committing the formula with
Ctrl+Shift+Enter will fill the formula in the entire range selected.
The results of the FREQUENCY formula show that a large number of customers purchase
between $200 to $300 per visit.
An alternative to the FREQUENCY function
If you attempt to delete one of the cells in the FREQUENCY formula range, Excel will tell
you that “You cannot change part of an array.” Excel treats FREQUENCY, and all array func-
tions, as one unit. You can change the whole array, just not individual cells within it. If
you want to change the bins, you have to delete and re-enter the array.
The COUNTIFS function can also be used to create a frequency distribution. Since
COUNTIFS is not an array formula, it’s easier to change the bins or expand or contract the
range. For the data in Figure 16.18, the COUNTIFS function is shown here:
=COUNTIFS($C$3:$C$52,">"&E3,$C$3:$C$52,"<="&F3)
FIGURE 16.18
Using the COUNTIFS function to create a frequency distribution
Unlike FREQUENCY, COUNTIFS needs the lower bound of the bin (column E). It counts all of
the values that are greater than the lower bound and less than or equal to the upper bound.
Rather than array entering this formula, it’s simply copied down for as many bins as we’ve
defined.
Feel free to revisit Chapter 13 for a more detailed look at the COUNTIFS function.
