Description
The Microsoft Excel FREQUENCY function returns how often values occur within a set of data. It returns a vertical array of numbers.
Syntax
The syntax for the FREQUENCY function in Microsoft Excel is:
FREQUENCY( data, intervals )
Parameters or Arguments
- data
- An array or range of values for which to count the frequencies.
- intervals
- An array or range of intervals that yoiu want to group the values in data.
Applies To
- Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003
Type of Function
- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel FREQUENCY examples and explore how to use the FREQUENCY function as a worksheet function in Microsoft Excel.
Let's start with some easy examples.
Based on the Excel spreadsheet above, the following FREQUENCY examples would return:
=FREQUENCY(B2:B10,D2) Result: 2 =FREQUENCY(B2:B10,D3) Result: 3 =FREQUENCY(B2:B10,D4) Result: 5 =FREQUENCY(B2:B10,D5) Result: 7 =FREQUENCY(B2:B10,89) Result: 7 (same as previous)
These examples simply look at the data found in cells B2:B10 and calculate all values that are lower than the second parameter. So in the case of:
=FREQUENCY(B2:B10,D5) Result: 7
There are 7 Test Scores in cells B2:B10 that are <= 89.
Example
Now let's take at a look at a more complex example involving array formulas.
When creating your array formula, you need to use Ctrl+Shift+Enter instead ofEnter when completing the formula. This creates {} brackets around your formula. This is very important to remember. If you do not have {} brackets around your formula, it is NOT being interpretted as an array formula by Excel.
Based on the spreadsheet above, the following Excel formula{=FREQUENCY(B2:B12,D2:D5)} into cells E2:E6 using Ctrl+Shift+Enter to complete the formula. This would return a vertical array with 5 values as follows:
The first value in the array would display in cell E2. The result would be 2 (because there are 2 Test Scores <= 59).
The second value in the array would display in cell E3. The result would be 1 (because there is 1 Test Score between 60 and 69).
The third value in the array would display in cell E4. The result would be 2 (because there are 2 Test Scores between 70 and 79).
The fourth value in the array would display in cell E5. The result would be 3 (because there are 3 Test Scores between 80 and 89).
The fifth value in the array would display in cell E6. The result would be 3 (because there are 3 Test Scores > 89). This catches all values greater than the last value in the interval.
Frequently Asked Questions
Question: In Microsoft Excel, I'm trying to use FREQUENCY to calculate the frequencies based on 5 minute intervals, but I can't seem to get the FREQUENCY function to group the values correctly. Here is a screen shot of what I have:
I'm using the formula:
{=FREQUENCY(B2:B12,D2:D9)}
As you can see, there are six "8:40:00 AM" values but they are showing up under the "8:45:00 AM" interval. Why is that?
Answer: The Microsoft Excel value "8:40:00 AM" is actually stored as the numeric value 0.361111111111111 with a repeating 1. Excel must be implicity rounding the time value during its FREQUENCY calculations. To solve this problem, round the data and intervals to 13 decimal places as follows:
{=FREQUENCY(ROUND(B2:B12,13),ROUND(D2:D9,13))}
Now if we look at the results, the FREQUENCY function seems to be grouping the time intervals properly and the six "8:40:00 AM" values are showing up under the correct interval.
