FREQUENCY Function (LibreOffice Calc)
The FREQUENCY function in LibreOffice Calc calculates how often values occur within specified bins. This guide explains syntax, array behavior, examples, histogram usage, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the FREQUENCY Function Does ▾
- Counts how many values fall into each bin
- Returns an array of results
- Automatically creates an extra bin for values above the last bin
- Ignores empty cells
- Works across sheets
- Ideal for histograms and distribution analysis
FREQUENCY is one of Calc’s most powerful statistical tools.
Syntax ▾
FREQUENCY(data_array; bins_array)
Where:
data_array— the values to analyzebins_array— the upper limits of each bin
FREQUENCY must be entered as an array formula in older Calc versions (Ctrl+Shift+Enter).
Modern versions spill automatically.
Modern versions spill automatically.
Basic Examples ▾
Count values in bins 10, 20, 30
=FREQUENCY(A1:A20; {10;20;30})
Returns an array of 4 values:
- Count ≤ 10
- Count 11–20
- Count 21–30
- Count > 30
Using a range for bins
=FREQUENCY(A1:A100; B1:B5)
Frequency across sheets
=FREQUENCY(Sheet1.A1:A100; Sheet2.B1:B5)
Advanced Examples ▾
Create a histogram (manual)
-
Define bins in B1:B5
-
Enter:
=FREQUENCY(A1:A100; B1:B5) -
Format results as a column chart
Frequency ignoring zeros
=FREQUENCY(IF(A1:A100<>0; A1:A100); B1:B5)
(Confirm with Ctrl+Shift+Enter in older versions.)
Frequency of visible cells only (filtered data)
FREQUENCY does not ignore hidden rows.
Use SUBTOTAL + helper column:
=SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0))
Then feed visible-only values into FREQUENCY.
Frequency by category (indirect)
=FREQUENCY(IF(B1:B100="North"; A1:A100); C1:C5)
Frequency of text categories (converted to numbers)
=FREQUENCY(MATCH(A1:A100; UNIQUE(A1:A100); 0); ROW(UNIQUE(A1:A100)))
Frequency for percentile grouping
=FREQUENCY(A1:A100; PERCENTILE.INC(A1:A100; {0.25;0.5;0.75}))
Frequency for equal-width bins
=FREQUENCY(A1:A100; SEQUENCE(10;1;0;10))
(10 bins of width 10.)
How FREQUENCY Calculates Bins ▾
Given bins {10;20;30}:
- Bin 1: values ≤ 10
- Bin 2: values >10 and ≤20
- Bin 3: values >20 and ≤30
- Bin 4: values >30
Bins must be sorted ascending.
Common Errors and Fixes ▾
FREQUENCY returns a single number
Occurs when:
- Entered without array behavior in older Calc
- Output range too small
Fix:
Select enough cells before entering the formula.
Err:502 — Invalid argument
Occurs when:
- Bins are not numeric
- Data contains non-numeric text
FREQUENCY returns unexpected distribution
Possible causes:
- Bins not sorted
- Zeros included
- Hidden rows included
- Text interpreted as zero
FREQUENCY spills too many results
FREQUENCY always returns one more result than the number of bins.
Best Practices ▾
- Always sort bins ascending
- Use helper columns for visibility‑aware frequency counts
- Use array formulas for conditional frequency
- Use FREQUENCY to build histograms and distribution charts
- Clean imported data before analysis
- Use named ranges for cleaner formulas
FREQUENCY is the backbone of histograms and distribution analysis — once you master it, you unlock a whole new level of statistical power in Calc.