Excel Data Analysis For Dummies
Book image
Explore Book Buy On Amazon

The FREQUENCY function in Excel counts the values in an array that fall within a range, or bin. The function uses the following syntax:


where data_array is the worksheet range that holds the values that you want to count and bins_array is a worksheet range that identifies the ranges of values, or bins, that you want to use to create a frequency distribution. Take a look at the following figure, for example.

A worksheet that illustrates how the FREQUENCY function works.
A worksheet that illustrates how the FREQUENCY function works.

To categorize the values in the worksheet range A2:A20 using the bins shown in B2:B6, select the worksheet range C2:C6 and enter the formula


Then press Ctrl+Shift+Enter to tell Excel that the function formula should be entered as an array. Excel enters your formula into each of the cells in the worksheet range C2:C6, with the result shown.

In cell C2, the function uses the bin value in cell B2 to count up all the data values greater than 0 and less than or equal to 80. In cell C3, the function counts up all the data values greater than 80 but less than 90, and so on. Note that you need to arrange your bin range values in ascending order.

About This Article

This article is from the book:

About the book author:

Paul McFedries is a true renaissance geek. He has been a programmer, consultant, database developer, and website builder. He's also the author of more than 90 books including top sellers covering Windows, Office, and macOS.

This article can be found in the category: