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 firstname.lastname@example.org 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...
I am a recent MS Accounting degree graduate and I am looking into a programming/IT related career. Anyone here have experience or know any accountants that diverted their careers into IT/Programming/System design, etc?...
Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
Upcoming CPE Webinars
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.
This material focuses on the principles of accounting for non-profit organizations’ expenses. It will include discussions of functional expense categories, accounting for functional expenses and allocations of joint costs.