When analyzing data, the task periodically arises to calculate the number of values that fall into the specified intervals “from and to” (in statistics, they are called “pockets”). For example, to calculate the number of calls of a certain duration when analyzing statistics on mobile communications in order to understand which tariff is more profitable for us:
To solve a similar problem, you can use the function FREQUENCY (FREQUENCY)… Its syntax is simple:
=FREQUENCY(Data; Pockets)
where
- Pockets – a range with the boundaries of the intervals, falling into which we are interested
- Data – a range with the original numerical values that we analyze
Please note that this function ignores blank cells and cells with text, i.e. only works with numbers.
To use the function FREQUENCY you need:
- prepare cells in advance with intervals-pockets of interest to us (yellow F2: F5 in our example)
- select an empty range of cells (G2: G6) one cell larger than the range of pockets (F2: F5)
- enter the FREQUENCY function and press the combination at the end Ctrl + Shift + Enter, i.e. enter it as an array formula
In all pre-selected cells, the number of hits in the specified intervals will be counted. Of course, you can use other methods to accomplish this task (COUNTIF function, pivot tables, etc.), but this option is very good.
In addition, with the FREQUENCY function, you can easily count the number of unique numbers in a set using a simple array formula:
- How to count the number of unique elements in a list
- How to make a list without repetition
- Frequency analysis of data using pivot tables and formulas