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 protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
Replies (11)
Please login or register to join the discussion.
Super helpful. Directions very easy to follow. Thank you!
Works with numbers but not with characters.
What is my data_array column was a character string (IE: names and I want the number of 'Joes' in the list)?
Jim,
Use the COUNTIF function to determine the number of Joes on the list. Let's assume this sample data:
A1: Joe
A2: Fred
A3: Joe
A4: Jim
A5: Joe
Enter the word Joe in cell C1, and then this formula in cell D1: =COUNTIF(A1:A5,C1) to return 3 for the number of Joes in the specified range.
Great question, I may write a follow-up article to elaborate further on using COUNTIF.
David Ringstrom
thanks David that is really helpful
I appreciate the feedback! And your comment reminds me that I never got around to writing about the COUNTIF function.
I have a sheet of part numbers in inventory by qtr by region, sub region, entity. I want to put the unique count overall in row 2 under qtr1-4 in cells B1:E1 on a separate sheet and then by region and then by sub region. I'm struggling with the multiple checks.
A pivot table actually sounds like the easiest means to accomplish what you're going for. You might have to add some helper columns to the right of your data with IF statements that would put a 1 in a column if it sold that quarter or 0 if it didn't. Those would then be the Values fields for your pivot table. Or you could use have the pivot table count the number of records instead of summing it. It's hard to go into more detail without seeing your spreadsheet first hand.
How can I do this for a range of numbers? For example: A=92-100, B=85-91, etc.
Thanks for the very clear directions. Some websites did not do that very well. Very much appreciated.
thanks...!!!
helpful! thanks!