Function Problems

How to Create a Frequency Formula in Excel and WPS Spreadsheet

Jason MillerJason Miller Nov 10, 2026 869 views

Question details

User needs to know what details and data parameters are required to create a FREQUENCY formula to analyze data distribution.

How to Create a Frequency Formula in Excel and WPS Spreadsheet
Product
Spreadsheet
Device & OS
not provided
Scenario
Calculating the frequency of values occurring within specific intervals to create a statistical distribution.
Observed behavior
Missing required parameters (data array and bins array definitions) to accurately calculate counts, unique values, or statistical distributions.
Before you start

Ensure you have your raw data organized in a single column (Data_array) and your desired interval thresholds in another column (Bins_array).

Solution 1Recommended

Understanding and Setting Up the FREQUENCY Function

Learn the required arguments for the FREQUENCY function to avoid calculation errors and get accurate interval counts.

The FREQUENCY function calculates how often values occur within a range of values, returning a vertical array of numbers. It requires two main components: the data_array (your raw data) and the bins_array (the intervals).

1
Prepare the Data_array

Consolidate the raw numerical data you want to count into a single column or range.

2
Define the Bins_array

Create a separate column specifying the upper limits for each interval you want to group the data into. Ensure these values are sorted in ascending order.

3
Select Output Cells

Select a range of adjacent blank vertical cells corresponding to your bins. It is recommended to select one extra cell below the bins to catch any values that are greater than your highest bin.

4
Enter the Formula

In the formula bar, type =FREQUENCY(data_array, bins_array) replacing the arguments with your actual cell ranges. Press Ctrl+Shift+Enter simultaneously to apply the array formula.

Understanding and Setting Up the FREQUENCY Function
Array Formula Requirement: The FREQUENCY function must be entered as an array formula. Pressing Ctrl+Shift+Enter will automatically enclose the formula in curly braces {}, allowing it to output results into multiple cells.
Calculate Frequencies Easily with WPS Spreadsheet

Master Data Analysis with WPS Office Free

WPS Spreadsheet provides full support for advanced array formulas like FREQUENCY, helping you analyze statistical distributions efficiently.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the raw data.
  2. 2. Set up your bins: Type your interval thresholds in a dedicated column to create the bins_array.
  3. 3. Apply the formula: Select the vertical output cells, type the FREQUENCY formula, and press Ctrl+Shift+Enter to calculate the distribution.
Fully compatible with Microsoft Excel formulas and array functionsAdvanced statistical data analysis capabilitiesUser-friendly interface for managing complex data arrays and binsCompletely free to download and use for your daily tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my FREQUENCY formula return a single value instead of an array?

This happens if you do not enter it as an array formula. Ensure you select the entire vertical range of output cells first, type the formula in the formula bar, and then press Ctrl+Shift+Enter.

What is a Bins_array in the FREQUENCY function?

The Bins_array is a set of intervals or thresholds you define. The function counts how many data points fall into each interval up to the specified limit.

Does the FREQUENCY function ignore blank cells?

Yes, the FREQUENCY function ignores blank cells and text values in your data array when calculating numerical distributions.

Why is there an extra number at the bottom of my FREQUENCY results?

The FREQUENCY function always returns an array with one more item than there are elements in your bins_array. This extra item returns the count of any values that are strictly greater than the highest bin value.