The FREQUENCY function always returns an array with one more item than bins in the bins_array. This is by design, to catch any values greater than the largest value in the bins_array. The general pattern for FREQUENCY is: where data_array and bins_array are typically ranges on the worksheet.
Instructions
To create a frequency distribution using FREQUENCY: In Excel 365, it is not necessary to enter FREQUENCY as an array formula. See notes below.
Examples
In the example shown, the formula in G5:G8 is: Entered as a multi-cell array formula. Note: the curly braces added by Excel automatically when entered with control + shift + enter.
Horizontal results
The FREQUENCY function always returns a vertical array of results. To return horizontal results, wrap the FREQUENCY function in the TRANSPOSE function:
Excel 365
In Excel 365, which supports dynamic arrays natively, it is not necessary to select multiple cells before entering the FREQUENCY function. In cell G5, you can simply enter the formula below: FREQUENCY will return an array of six counts, and these counts will spill automatically into the range G5:G9. The count in the last row (G9) is the overflow bin, the count of any values greater than the largest value in the bins_array.
Notes
FREQUENCY returns multiple values and must be entered as an array formula, except in Excel 365. FREQUENCY always returns an array with one more item than bins. This is by design, to catch any values greater than the largest interval in the bins_array. Each bin shows a count of values up to and including bin value, excluding values already accounted for.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
