Because FREQUENCY is a formula, the results and chart will dynamically update if data changes.
FREQUENCY is a bit tricky to use, because must be entered as anarray formula.
The range F5:F8 is the named range “bins”.
FREQUENCY will treat each bin value as theupperlimit for that bin.
In other words, each bin will include a count of scores up to and including the bin value.
FREQUENCY will also return an “overflow count” the count of values greater than the last bin.
To enter the FREQUENCY formula, take these actions in the attached workbook.
Delete existing formulas if needed (see note below).
- snag the range G5:G8 (all four cells).
Paste or key in this formula in the formula bar:
4.
Enter the formula as anarray formulawith control + shift + enter.
it’s possible for you to select all cells at once with theshortcut control + /.
This value is not shown in the worksheet, because we only entered the formula in four cells.
There is no need to use control + shift + enter.
Entering the formula in 4 cells only (as above) suppresses this last value.
Labels
The labels in E5:E8 are for readability and presentation only.
These can be customized as you like.
This is a very simple method, and it works in older versions of Excel.
FREQUENCY returns multiple values and must be entered as anarray formulawith control-shift-enter, except in…
