Chapter 16: Using Formulas for Statistical Analysis
16
Creating a Frequency Distribution
Quartiles are a popular way to group data into bins, which is why Excel has a dedicated
QUARTILE function. Sometimes, however, you may want to group your data into bins that
you define. Figure 16.17 shows a partial list of 50 invoices and the total amount sold on
each invoice. We want to determine how common it is for our customers to make purchases
between $1 and $100, $101 and $200, and so on.
FIGURE 16.17
Calculating the frequency with custom bins
Excel’s FREQUENCY function will count all the invoices that fall within the bins we define.
=FREQUENCY(C3:C52,F3:F12)The FREQUENCY function is an array function. This means that instead of pressing Enter
to commit the formula, you must press Ctrl+Shift+Enter. Excel will insert curly braces ({})
around the formula to indicate that it has been array-entered.
We explore array functions in detail in Chapter 18.FREQUENCY takes two arguments: a range of data to be grouped into bins and a range
of numbers that represent the highest amount for that bin. First, enter the bin values in