Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Using Excel’s Frequency function

by
4th May 2010
Save content
Have you found this content useful? Use the button above to save it to your profile.

In another selection from his series of tutorials on AccountingWEB.com, David Ringstrom illuminates the uses of Excel’s FREQUENCY function.

Excel's FREQUENCY function comes in handy for situations where you want to find out how many values fall within specific ranges. A property portfolio manager, for example, might want to check how many buildings fall within different criteria for floor areas such as 250,000, 500,000, and 1m square feet. Or an examiner might want to show how many students scored particular grades on their exams.

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.
 
We’ll start with the simple scenario of a teacher who wants to determine how many students earned a specific grade in their exam. Columns A and B below contain student names and grades. Columns D and E contain letter grades and the top value of each letter grade range:

E5 are 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 here:
 
Select cells F1 through F5 before you type the FREQUENCY function.
 
Once you've selected cells F1 through F5, type the formula as illustrated below 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.
 
Ctrl-Shift-Enter after you complete the FREQUENCY function.

 
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.
 
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. Here is what a typical worksheet would look like for this example:

Property floor area worksheet using FREQUENCY function

About the author
David Ringstrom CPA is the head of Accounting Advisors, an Atlanta-based software and database consulting firm. He contributes articles on Excel to Microsoft Professional Accountant's Network newsletter, speaks at conferences and is fond of saying "Either you work Excel, or it works you!" He markets a range of online training materials and can be reached by email at david[AT]acctadv.com
 

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.