What's the FREQUENCY? Using Excel's FREQUENCY function
by AccountingWEB on
printer friendly
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 david@acctadv.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...
How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...
Event Date: October 30, 2014, 2 pm ETMany Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word...
Event Date: September 9, 2014, 2:00 pm ETIn this session we'll discuss the types of technologies and their uses in a small accounting firm office. Included will be:The networked office: connecting everything together for...
Upcoming CPE Webinars
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.