XL: How to Use the FREQUENCY Function
ID: Q100122
|
The information in this article applies to:
-
Microsoft Excel 98 Macintosh Edition
-
Microsoft Excel 97 for Windows
-
Microsoft Excel for Windows 95, versions 7.0, 7.0a
-
Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
-
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
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:
- 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
- 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
- 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.
- 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:
- 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
- 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
- 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.
- On the Formula menu, click Paste Function.
- Click the Paste Arguments check box to select it.
- Click the FREQUENCY function and click OK to paste the function into
the spreadsheet.
- In the formula bar, select the data_array argument and replace it
with the range reference or a named range that contains your data.
- 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)
- 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
|