The Frequency Function
I have a list of sales figures from a single month and woud like to know how many were under a particular number, ie 1000, and then how many numbers fell in between 2000, 3000, and so on?
RE: The Frequency Function
Answer to the question:
To use the Frequency function you need to do a little set up work;
1. Add another column of data in this case the values that you wish to be your numbers starting with 1000, then 2000, 3000 etc. This is known as your Bins Array, and the values are the categories or "Bins" that you want Excel to use for the intervals.
2. Highlight the range where you want the frequency analysis to appear, (this will be in another column, preferably alongside the bins array, include one extra cell in this column for numbers that are higher than your final bin.
3. Type the frequency in the top cell using this syntax; =FREQUENCY(VALUES RANGE, BINS ARRAY RANGE) e.g. =Frequency(A7:D19,F7:F20) DO NOT PRESS ENTER.
4. Once you have typed the Frequency function, Hold down both CTRL and SHIFT keys and then press ENTER.
This creates an array formula and simultaneously analyses all of your bins, giving a count of the number of entries below each frequency level.
You can recognise the formula by the braces {} around the entire formula.
Note that each number in the bins array means less than or equal to.
