The `FREQUENCY` function in Excel is used to count the number of times values occur within a range or dataset and to group them into specified intervals, known as bins or classes. This can be particularly useful for statistical analysis and data visualization. Here’s how you can use the `FREQUENCY` function:
Syntax
FREQUENCY(data_array, bins_array)
- data_array: The range of cells that contains the values you want to count.
- bins_array: The range of cells that contains the bins or intervals you want to group the data_array into.
Important Points
- The `FREQUENCY` function returns an array of numbers indicating how many values in the `data_array` fall into specific bins in `bins_array`.
- It needs to be entered as an array formula, especially in older versions of Excel before Office 365, which requires pressing `Ctrl + Shift + Enter` instead of just `Enter`.
- The last element in the result will show the count of values in `data_array` that are above the highest value in `bins_array`.
Steps to Use FREQUENCY
- Setup Your Data: Prepare your data and decide on the bins. For example, if you have test scores ranging from 0 to 100, you might choose bins like 10, 20, 30, etc.
- Select the Output Range: Select a vertical range of cells where you want the frequency counts to appear. The range should have one more cell than the number of bins to accommodate the count of values that exceed the largest bin.
- Enter the Function: Type `=FREQUENCY(` then select the `data_array`, add a comma, and select the `bins_array`. The formula might look something like `=FREQUENCY(A1:A100, D1:D10)`.
- Complete as an Array Formula: If you’re using Excel 365 or Excel 2016 and later, just press `Enter`. For earlier versions, press `Ctrl + Shift + Enter`.
- View Results: The selected range will be filled with the frequency distribution of your data.
Example
Suppose you have sales data in column A (A2:A20), and you want to know how frequently sales amounts fall into specified ranges. If cells D2:D5 contain your bin limits (e.g., 100, 200, 300, 400), you can:
- Select cells E2:E6.
- Enter the formula: `=FREQUENCY(A2:A20, D2:D5)`.
- Press `Ctrl + Shift + Enter` to complete it as an array function.
- Cells E2:E6 now show the count of sales amounts within each defined range (0-100, 101-200, etc.).
By following these steps, you’ll be able to effectively use the `FREQUENCY` function to analyze and organize your data in Excel.
Unlock Your Potential
Excel
Access
Power BI
