Hello and welcome back to the Excel Tip of the Week! This week, we have a Developer post in which we are taking a look at the helpful FREQUENCY function, which can quickly count how many entries in a set of data fall into a number of bins, or ranges, of results.
This Tip is marked as a Developer-level tip because FREQUENCY is an array function (see last week's post for more); however it is a pretty simple one to write so even less experienced Excel users should have no trouble with it.
What FREQUENCY is for
Let's see we have a range of values - I'm using sales invoices - that we want to analyse. We might want to create a frequency distribution - i.e. figure out how often values in a number of ranges occur. Our ranges may or may not be the same size.
It is possible to make a complex COUNTIF to get the result that we want - but we can use a simple FREQUENCY array function to achieve the same result:
How to write a FREQUENCY function
The syntax is pretty straightforward:
=FREQUENCY(range of data, range of bins)
Range of data - This is the range that contains the raw date (column H in the above example)Range of bins - This is the range that contains the values for the thresholds for the bins (K2:K5 in the above example). The bins must be ordered, but can be in either ascending or descending order.
Note that the array function FREQUENCY is entered in several cells at once: specifically, it is entered by selecting the range L2:L6, then entering the function, and then confirming the array with Ctrl + Shift + Enter. This enters a single array function in those five cells. The FREQUENCY must be one cell larger than the range of bins; the final cell will contain the number of data items which fall beyond the final written threshold.
Because FREQUENCY is a single function that occupies a range of cells, you can't edit those cells individually. You will get an error message if you try to edit / delete just one of the cells in the range, or try to insert or delete a row. You must edit or delete all the cells at once if you wish to amend the array.
You can see the Excel file used to make the above demonstration attached to this post.
Previous post - Revisiting array formulasTOTW index Next post - Revisiting circular references
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.