What's the FREQUENCY? Using Excel's FREQUENCY function
by AccountingWEB on
By David H. Ringstrom, CPA
From time to time you may want to determine how items fall within specific ranges. For instance, a teacher may wish to know how many students earned A's, B's, C's, and so on. A real estate portfolio manager may wish to know how many buildings fall within ranges of 250,000, 500,000, and one million square feet, respectively. In such cases, Excel's FREQUENCY function is the right tool for the job.
The FREQUENCY function has two arguments:
- data_array – a range of cells containing numeric values
- bins_array –a range of cells containing bins into which the numeric values should be grouped
FREQUENCY has a special characteristic, in that you don't simply type it into a cell and press Enter. Instead, you must select the cells where you want to put the FREQUENCY function, type the formula, and then press Ctrl-Shift-Enter. If you simply press Enter, then FREQUENCY may return an incorrect result. Let's look at a simple example.
As shown in Figure 1, let's assume that a teacher wishes to determine how many students earned each letter grade. Columns A and B of Figure 1 contain student names and grades. Columns D and E contain letter grades and the top value of each letter grade range.
Figure 1: Cells B1:B10 will serve as the data_array, while cells E1:E5 will serve as the bins_array.
Now that we've established our data, we're ready to use the FREQUENCY function. First, we'll select cells F1 through F5, as shown in Figure 2.
Figure 2: Select cells F1 through F5 before you type the FREQUENCY function.
Once you've selected cells F1 through F5, type the formula shown in Figure 3, and then press Ctrl-Shift-Enter. FREQUENCY will not function correctly if you simply press Enter and try to copy the formula to the adjacent cells.
Figure 3: Be sure to press Ctrl-Shift-Enter after you complete the FREQUENCY function.
As shown in Figure 4, when you press Ctrl-Shift-Enter, Excel fills cells F1 through F5 with the FREQUENCY function. Also notice the curly brackets that Excel adds around the formula. These indicate an array function, and these brackets can only be added by pressing Ctrl-Shift-Enter after you type or edit the formula. Unlike most formulas that aggregate results into a single cell, FREQUENCY requires you to select a multiple cell range in order to function correctly.
Figure 4: Excel automatically fills cells F1 through F5 and adds curly brackets around FREQUENCY.
However, FREQUENCY isn't just for teachers. As I mentioned at the start of the article, a portfolio manager might wish to know how many buildings are defined as small, medium, or large, based on square footages of say 250,000, 500,000, and one million. Figure 5 shows an example.
Figure 5: Press Ctrl-Shift-Enter after you enter the formula shown in Figure 5.
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at email@example.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
You may like these other stories...
Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
Upcoming CPE Webinars
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.