Frequency Formula in Excel (Table of Contents)
Guide to Frequency Formula in Excel
Frequency formula in excel is nothing but how often some things occur or some numbers or values appear in the data range. For an example look at the below set of numbers.
25, 23, 20, 30, 40, 21, 22.
In the above data frequency of the number 23 is 3 i.e. numbers appearing less than or equal to 23 appeared 3 times.
We have built-in excel function FREQUENCY which will calculate the frequency number how often they appear in the data points.
The formula of the Excel FREQUENCY function includes two parameters.
- Data Array: This is the range of cell which contains numbers or series of numbers.
- Bins Array: For this array, we are trying to find the frequency values.
How to Use Frequency Formula in Excel (with Examples)
Below are some of the examples of Frequency Formula in Excel.
Now take a look at the examples of FREQUENCY formula in excel. Assume below is the data you are working on.
From the above data, we need identity how many times numbers are appearing less than the number 24.
Step 1: Open FREQUENCY formula.
Step 2: Data Array is our number list, so select from A1 to A6.
Step 3: Now Bins Array is nothing but our limit number i.e. 24. Enter 24 as the argument and close the formula.
So numbers which are less than or equal to 24 appeared 3 times in the selected data range. So the frequency of number 24 is 3.
Now take look at one more example. When we want to find the frequency for many numbers we cannot change the Bins Array every time rather we can make it a cell reference and apply excel FREQUENCY as an array formula. For example, consider below numbers for demonstration.
From the above list we need to count numbers appearing from 0 to 20, 21 to 40, 41 to 60, 61 to 80, and >80.
Step 1: Firstly select cells from D3 to D7.
Step 2: Now select data array as A1 to A18 range.
Step 3: Now select bins array as C3 to C7.
Step 4: Since it is an array formula we need to close the formula by holding Ctrl + Shift + Enter.
As soon as you type this keys it will insert the formula to all the selected cells i.e. from D3 to D7.
So we got the result here. Numbers appearing from 0 to 20 is 3 times, 21 o 40 numbers appearing 3 times, 41 to 60 numbers appearing 3 times, 61 to 80 appearing 2 times, and numbers greater than 80 appearing 7 times.
Like this using frequency formula, we can find how often the bins array occurring in the data array.
We have seen how to calculate frequency levels of bins array in the data array. Now we will see how to calculate the frequency percentage.
Frequency percentage is nothing but each bin appearance percentage in the data array. For example, take the above example only.
Here we have already calculated the frequency formula now enter one more column next to it and say Frequency %.
Now apply below formula to find the percentage frequency.
Convert the result to percentage format.
COUNTIFS as the Alternative to FREQUENCY Formula
To find out how often the data appears in the data array, we do not necessarily use excel frequency formula. We can use the COUNTIFS function as an alternative method.
For this prepare below table first.
Step 1: Open COUNTIFS function.
Step 2: Select the criteria range as A1 to A18.
Step 3: Apply the criteria below.
Step 4: Again select the range A1 to A18 as Criteria Range 2.
Step 5: Apply below criteria for criteria range 2.
Step 6: Hit enter and copy paste the formula to other cells.
We got the same result as the Excel FREQUENCY functions returned.
Things to Remember
- When more than one bins array selected we need to close the formula as an array formula.
- In case of more than one bins array, we need to select the range of cells first then enter the Excel Frequency formula.
- FREQUENCY do not consider blank cells.
- Each bin result is equal to the bin value.
This has been a guide to Frequency Formula in Excel. Here we discuss how to use Frequency formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –