Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

FREQUENCY

Return count of numbers falling within a range Excel
Count all number that fall within each bin

Rational

Return the count of each value occurs in range.

Description

The Excel FREQUENCY function returns the total number of occurrences of a value in a given bin of data set. For example, if A1:A300 contains a list of numbers ranging between 1-100 and user wants to find the occurrence within 11 to 20 bins.

Syntax

=FREQUENCY(data_array, bins_array)

Parameters    

data_array – Range of data to get the frequency from.

bins_array – A series of Interval (bins) that divide the entire range of value.

Return Value

Count of values per bin

Key Notes

  • The FREQUENCY function is an Array function that returns multiple results falling between each bin at once.
  • The Excel COUNTIF or COUNTIFS function is alternative to the FREQUENCY function.
  • The function ignores empty cells and text in data_array.
  • Note: The function returns the top of each bin; like a 20 bin is for holding the count of how many values fall between 11 and 20.
  • Caution: Make sure the bin values are in ascending order.
  • Cannot change part of the Array formula i.e., one cell of a range. You have to update from the first cell and then Ctrl+Shift+Enter.Excel return error Array
  • Select all Excel cell in advance to return value.
    Highlight all cells for array formula
    Highlight all the cell in advance (G5:G15) and Ctrl+Shift +Enter
  • In the case of a duplicate bin, the function returns zero for the next interval. This is an important aspect which is used for creating a robust formula.

  • The FREQUENCY function only returns results vertically i.e., all counts in one column. Use the TRANSPOSE function to get results horizontally.

    Use TRANSPOSE function to return results Horizontally
    Use the TRANSPOSE function to return results horizontally

Keep Reading Similar Functions:

Return the dynamic operations to the summary value in Excel

SUBTOTAL

The SUBTOTAL is a versatile function that summarizes a list or dataset using 11 different operations by determining the actual function based on the user input.

Read More »
Count reference cells based on criteria in Excel

COUNTIF

The COUNTIF function returns a count of number based on given criteria like =COUNTIF(A1:A5,”>5″) that count numbers in the list >5 and returns 3.

Read More »
Count reference cells based on multiple criteria in Excel

COUNTIFS

The COUNTIFS function returns a count of numbers based on one or multiple given criterion based on dates, numbers, and text.

Read More »