Using Array Formulas for Frequency Distribution

Frequency distribution is displaying how often something falls into a certain category. For example, suppose you had a range of age groups (categories). Like this:

25 and under
26 to 35
36 to 45
46 to 55
56 and over

Your company is bringing out a new drink. What you want to know is which age groups liked your new drink the most. You could then target your advertising at the age groups that like your new drink. After all, you don't want to waste your advertising dollars on age groups that hated your drink. Let's see how to use Frequency Distribution to display the information collected from a survey.

The first thing you need is some data. Download the following Excel spreadsheet:

Frequency Distribution Spreadsheet (Right click and select File Save/Save As)

Save it to your computer, and then open it up. The data is all in the A column, and is a list of people who liked the drink, by age. The first thing you need to do for Frequency Distribution is to lay out your groups (called Bins in Excel).

  • Click inside cell C1 and type the word BINS
  • Click inside cell D1 and type the word FREQUENCY:
  • Your spreadsheet should then look like this:

Spreadsheet showing data  for the Frequency Distribution

  • Enter the following numbers in the BINS column: 25, 35, 45, 55, 65:

Some numbers entered to use as BINS

When you are entering your bins, you only need one number. So you don't need to type "under 25", or "26 to 35", etc. Excel take the first single number to mean 0 to 25. For the 35, Excel takes it to mean "add 1 to the previous bin (25) then go up to 35. For the 45, Excel then goes from 36 to 45, and so on. (Our bins all go up in groups of 10. But you don't have to go up in stages of 10. You can pick any number you like for your stages.)

We now need to work out how many people liked the drink and were under 25, how many liked it and were aged 26 to 35, etc. There's an Excel function that can do all the calculations for us. That function is called FREQUENCY. The FREQUENCY function takes a list of data (an array) and an array of bins, and puts all the numbers in the correct categories.

To see how it works, highlight the cells D2 to D6.

D2 to D6 is where Excel will keep track of how many people belong in each group.

Now click inside of the formula bar and type the following formula (DON'T press the enter key on your keyboard just yet):


The Frequency function in Exce;

The FREQUENCY function needs two arrays. The first array is your data, which for us is in the A column, in cells A2 to A81. The second array that the FREQUENCY function needs is your bins. Our bins are in the cells C2 to C6. A comma separates the two arrays.

Once you have typed the formula, hold down the SHIFT and CTRL keys. Keep them held down and then press the enter key on your keyboard. This will create an array formula. Your spreadsheet will then look like this:

The Frequency function used in an array formula

So our FREQUENCY array formula has worked out that 34 people aged 25 or under liked the drink. In the age group 26 to 35, 18 people liked it. Of those who are aged 36 to 45, 12 people liked the drink. 6 people aged 46 to 55 liked it, and 8 people over 65 like the drink. Clearly, you'd want to target your advertising at those who are 35 and under.

You can create a chart out of all this information.

Highlight the cells C1 to D6:

Highlighting data to use in a Frequency Distribution chart

From the Insert ribbon at the top of Excel, select a 2-D Column Chart:

The Charts panel in Excel

Selecting a 2-D Column chart in Excel

The chart will then look like this on your spreadsheet:

A 2-D column chart

However, this is not quite what we're looking for. Excel has taken the numbers in the Bins column and the numbers in the Frequency column and created two sets of bars in the chart. We only want single bars. We want the Bins at the bottom (Horizontal Axis) and Frequency on the left side (Vertical Axis).

To remedy the situation, right-click on the chart. From the menu that appears, choose Select Data:

The Select Data menu option

When you click on Select Data, you should see a dialogue box appear. This one:

The Select Data Source dialogue box

Click the Edit button, under Horizontal (Category) Axis Labels. You'll then see this box appear:

The Axis Labels dialogue box

Go back to your spreadsheet and select the cells C2 to C6:

Select cells for the Axis Labels

Now click OK on the Axis Labels dialogue box. The Select Data Source dialogue box will then look like this:

Select Data Source dialogue box showing edited Axis Labels

On the left-hand side, under Legend Entries (Series), highlight the BINS entry. Then click the Remove button:

Select Data Source dialogue box showing  Legend Entries

This should leave you with just the Frequency entry. Click OK, and your chart should now look like this:

Excel Chart showing a frequency distribution

You can change the titles at the top and bottom so that it looks like ours:

To change the Frequency title at the top, double click the text and type something else. To change the Frequency series title at the bottom, click inside of cell D1 on your spreadsheet. Type Age Groups instead of Frequency.

And that's it for Frequency Distribution. If you have a lot of data that can be grouped into categories, then a Frequency Distribution count, along with a chart, can make all the difference. In the next lesson below, you'll learn about Web Integeration