XL: How to Use the FREQUENCY Function

ID: Q100122

The information in this article applies to:

SUMMARY

The FREQUENCY function returns a frequency distribution as a vertical array. For a given set of values and a given set of bins (or intervals), a frequency distribution counts the number of values that occur in each interval.

The FREQUENCY function syntax is as follows: 


   FREQUENCY(data_array, bins_array)


The function returns the number of elements you specify in the data_array argument that fall within the intervals you specify in the bins_array argument.

NOTE: The frequency array returned by the FREQUENCY function contains one value more than the number of values in your bins array. For example, if you have three numbers in the bins array, the FREQUENCY function returns four values.


MORE INFORMATION

When you specify the range of cells that contains your data in the FREQUENCY formula, you can either enter each value separated by a comma, enter the reference of the range that contains your data, or enter a named range. The same rule applies when you enter the information for your bins array.

When you enter the function, press CONTROL+SHIFT+ENTER (in Microsoft Excel for Windows) or COMMAND+ENTER (in Microsoft Excel for the Macintosh). This key combination is required when you enter any array in Microsoft Excel.

Using the FREQUENCY Function in Excel Versions 5.0 and Later

To use the function, follow these steps:

  1. In a column in a worksheet, type the data set from which you will obtain a frequency distribution. For example, type the following: 

    
      A1:   2
      A2:   5
      A3:   8
      A4:  11
      A5:  12
      A6:  19
      A7:  21
      A8:  32
      A9:  45
     A10:  48


  2. In another column, type the data intervals for which you want to obtain frequencies. For example, type the following: 

    
      B1:  10
      B2:  20
      B3:  30
      B4:  40


  3. In another column, select a vertical range of blank cells in which the range of cells you select contains one cell more than the number of values in your bins array.

    For example, if the bins array contains four values, select five cells.

    Continuing with the example, select C1:C5.


  4. Type the following formula

    =FREQUENCY(A1:A10,B1:B4)

    and then press CONTROL+SHIFT+ENTER or COMMAND+ENTER (in Microsoft Excel for the Macintosh) to enter this formula as an array formula.


The formula in this example returns the following: 


   C1: 3
   C2: 3
   C3: 1
   C4: 1
   C5: 2


Using the FREQUENCY Function in version 4.0

To use the function, follow these steps:

  1. In a column in a worksheet, type the data set from which you want to obtain a frequency distribution. For example, type the following: 

    
      A1:   2
      A2:   5
      A3:   8
      A4:  11
      A5:  12
      A6:  19
      A7:  21
      A8:  32
      A9:  45
     A10:  48


  2. In another column, type the data intervals for which you want to obtain frequencies. For example, type the following: 

    
      B1:  10
      B2:  20
      B3:  30
      B4:  40


  3. In another column, select a vertical range of blank cells in which the range of cells you select contains one cell more than the number of values in your bins array. If the bins array contains four values, select five cells. For example, select cells C1:C5.


  4. On the Formula menu, click Paste Function.


  5. Click the Paste Arguments check box to select it.


  6. Click the FREQUENCY function and click OK to paste the function into the spreadsheet.


  7. In the formula bar, select the data_array argument and replace it with the range reference or a named range that contains your data.


  8. In the formula bar, select the bins_array argument and replace it with a range reference or a named range that contains your bin values.

    The formula in the formula bar should look like the following:

    =FREQUENCY(A1:A10,B1:B4)


  9. After you select the data and bins arrays, press F2, and then press CONTROL+SHIFT+ENTER (in Microsoft Excel for Windows) or COMMAND+ENTER (in Microsoft Excel for the Macintosh) to enter the formula as an array.


The formula returns an array that contains the following: 


   3, 3, 1, 1, 2

Additional query words: distribution bins cumulative 4.00 4.00a 4.0a 5.00a 5.00c howto 8.00 97 98 XL98 XL97 XL7 XL5 XL4 


Keywords          : xlformula 
Version           : WINDOWS:4.0,4.0a,5.0,5.0c,7.0,97; MACINTOSH:4.0,5.0,5.0a,98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 6, 1999