FREQUENCY

Statistical Functions

(4.7/5)

Calculates how often values occur within a range of values and returns a vertical array of numbers. FREQUENCY is an array function that creates frequency distribution (histogram) data. Essential for statistical analysis, data distribution analysis, creating histograms, and understanding value frequency patterns.

Interactive Formula Tester

Data|Bins:

Formula:

=FREQUENCY("1,2,3,4,5,6,7,8,9,10|3,6,9")

Complete Theory & Understanding

Master the fundamentals of Excel FREQUENCY function

Core Concept

The FREQUENCY function calculates how often values occur within specified ranges (bins) and returns a vertical array of frequency counts. FREQUENCY is an array function that creates frequency distribution data, essential for histograms and statistical analysis. It counts values in each bin: values ≤ first bin, values > first bin and ≤ second bin, etc. The output array has one more element than bins_array to count values above the highest bin. FREQUENCY returns a vertical array and must be entered as an array formula. Essential for statistical analysis, data distribution, histogram creation, and understanding value frequency patterns.

Why Use FREQUENCY?

Create histogram frequency data
Analyze data distribution patterns
Frequency in statistics
Classify values into ranges

Key Characteristics

Array Function

Returns vertical array of counts

FREQUENCY returns multiple values

Output Size

n+1 elements for n bins

3 bins → 4 output elements

Vertical Array

Always returns column

Cannot be horizontal

Bin Intervals

Counts values in each interval

≤bin1, >bin1≤bin2, etc.

Function Anatomy

=FREQUENCY(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Histogram Creation

Create histogram frequency data

Distribution Analysis

Analyze data distribution patterns

Statistical Analysis

Frequency in statistics

Data Classification

Classify values into ranges

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=FREQUENCY(data_array, bins_array)

Required

data_array:

Array of values for which you want to count frequencies.

Required

bins_array:

Array of intervals (bins) into which you want to group the values in data_array.

Returns

Return Value:

Vertical array of frequency counts

Description: Returns a vertical array of numbers representing frequency distribution

Interactive Examples

Basic FREQUENCY

Create frequency distribution

Input Text:

"Data: 1,2,3,4,5,6,7,8,9,10; Bins: 3,6,9"

Formula:

=FREQUENCY(A1:A10, B1:B3)

Result:

{3;3;3;1}

Returns array showing counts: 3 values ≤3, 3 values >3 and ≤6, 3 values >6 and ≤9, 1 value >9. FREQUENCY returns one more element than bins (for values above highest bin).

VBA Implementation & Automation

Basic FREQUENCY in VBA

Use FREQUENCY function in VBA

' Basic FREQUENCY in VBA
Dim dataRange As Range
Dim binsRange As Range
Dim resultArray As Variant
Set dataRange = Range("A1:A10")
Set binsRange = Range("B1:B3")

' FREQUENCY returns array - assign to variant
resultArray = Application.WorksheetFunction.Frequency(dataRange, binsRange)

' Output to worksheet (vertical array)
Range("D1").Resize(UBound(resultArray, 1), 1).Value = resultArray

' Create frequency distribution
Sub CreateFrequencyDistribution()
    Dim dataRange As Range
    Dim binsRange As Range
    Dim resultArray As Variant
    Dim i As Integer
    
    Set dataRange = Range("A1:A20")
    Set binsRange = Range("B1:B5")
    
    ' Get frequency array
    resultArray = Application.WorksheetFunction.Frequency(dataRange, binsRange)
    
    ' Output results starting at D1
    Range("D1").Resize(UBound(resultArray, 1), 1).Value = resultArray
    
    ' Label the results
    Range("C1").Value = "Bin"
    Range("C2").Resize(UBound(resultArray, 1), 1).Value = "Frequency"
End Sub

' Process frequency results
Sub ProcessFrequencyResults()
    Dim dataRange As Range
    Dim binsRange As Range
    Dim resultArray As Variant
    Dim i As Integer
    Dim totalCount As Long
    
    Set dataRange = Range("A1:A100")
    Set binsRange = Range("B1:B10")
    
    resultArray = Application.WorksheetFunction.Frequency(dataRange, binsRange)
    
    ' Verify total
    totalCount = 0
    For i = 1 To UBound(resultArray, 1)
        totalCount = totalCount + resultArray(i, 1)
    Next i
    
    ' Output results
    Range("D1").Value = "Bin Range"
    Range("E1").Value = "Frequency"
    Range("D2").Resize(UBound(resultArray, 1), 1).Value = resultArray
    Range("F1").Value = "Total: " & totalCount
End Sub

' Create histogram data
Sub CreateHistogramData()
    Dim dataRange As Range
    Dim binsRange As Range
    Dim resultArray As Variant
    Dim outputRange As Range
    
    Set dataRange = Range("A1:A50")
    Set binsRange = Range("B1:B5")
    Set outputRange = Range("D1")
    
    resultArray = Application.WorksheetFunction.Frequency(dataRange, binsRange)
    
    ' Output with labels
    outputRange.Value = "Frequency"
    outputRange.Offset(1, 0).Resize(UBound(resultArray, 1), 1).Value = resultArray
End Sub

Business Applications

Histogram Creation

Create histogram frequency data

=FREQUENCY(data, bins)

Distribution Analysis

Analyze data distribution patterns

=FREQUENCY(test_scores, grade_bins)

Statistical Analysis

Frequency in statistical analysis

=FREQUENCY(sample_data, intervals)

Data Classification

Classify values into ranges

=FREQUENCY(values, category_bins)

Common Issues & Solutions

#N/A Error

FREQUENCY returns #N/A

Select output range first, then enter FREQUENCY formula

Solution: FREQUENCY is an array function. You must select the output range first (one more cell than bins), then enter the formula. In newer Excel, just press Enter; in legacy Excel, press Ctrl+Shift+Enter.

Wrong Output Size

Not getting expected number of results

Output cells = bins + 1

Solution: FREQUENCY returns n+1 elements where n = number of bins. If you have 3 bins, select 4 cells for output. The extra element counts values above the highest bin.

Horizontal Output

Need horizontal instead of vertical

Use TRANSPOSE(FREQUENCY(...)) for horizontal

Solution: FREQUENCY always returns a vertical array. To get horizontal, wrap with TRANSPOSE: TRANSPOSE(FREQUENCY(data, bins)). Or rearrange after calculation.

Array Formula Not Working

Formula not recognized as array

Select range first, then Ctrl+Shift+Enter (legacy)

Solution: In newer Excel (365/2021), array formulas work automatically. In older Excel, select the entire output range first, type the formula, then press Ctrl+Shift+Enter. All output cells will show {braces}.