| | Feedback | Links | Array Functions | Statistics and QUARTILEs | | ||
This useful function can analyse a series of values and summarise them into a number of specified ranges. For example the heights of some children can be grouped in to four categories of [Less than 150cm]; [151 - 160cm]; [161 - 170cm]; [More than 170cm].
FREQUENCY() is an unusual array function and it works differently to most other normal functions. It can not simply be typed into a cell or even entered properly using the Excel Function Wizard.
Note that this function does not analyse values into categories e.g. household expenditure into groups such as gas, electricity, water, rates etc. To perform this kind of analysis an Advanced Filter may be appropriate.
The frequency function has two arguments - the first is the range of cells containing values to be analysed; the second is the range of cells containing the upper values of each group banding. e.g. =FREQUENCY(A3:A120, B6:B10)
The second argument (the group upper limits) will exclude any values which exceed the highest category or banding. The function allows you to take account of this and extend the range of analysis to an additional category which contains all values that exceed the specified upper limit.
|
1. Identify a range of data on which you want to perform your frequency analysis. (C5:C16) 2. Create a vertical range of cells containing the upper bandings with which you wish to group your data (i.e. 0-12, 13-16, 17-20, 21-24) 3. Highlight a vertical range of cells in which you wish the analysis to be reported (F5:F9). Note that by extending this output range by one extra cell, you can capture all values above the maximum banding (i.e. over 24). |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4. With the whole output range highlighted, type the
formula. = FREQUENCY(C5:C16, E5:E8) 5. Do not press enter. This is an array function and therefore requires you to use the alternative key strokes of <Ctrl> + <Shift> + <Enter> |
|
6. The results will then be automatically entered in all 5 cells (F5:F9). You will notice (in the formula bar) that the formula appears with curly brackets / braces '{}' around it. This indicates that it is an array function. If you type the formula in a single cell and don't enter it using the correct key combination, the function will only return the first value (i.e. 3). | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
7. It is not necessary to have the output range adjacent to the range of bandings. It can be placed anywhere so long as the number of cells is sufficient. You may wish to place the results directly into a table which contains narrative text versions of the bandings for display purposes. In the final illustration (left), the range of values used to define the bandings used by the function is hidden elsewhere and need not be displayed or printed. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This function can be of assistance in grouping data to be displayed on a graph.
The (Excel Web App) example shown below can be played with here and you can edit some of the values. If you want to view the formulas, select the [X] button to open it in your own Excel.
A Microsoft Excel Web App example of the Frequency function.
 |
Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: xlfreq.htm | © meadinkent.co.uk 2016 | Page last updated Jan14 | CMIDX S1 P6 Y |