excel 2003 visual basic intermediate course - the frequency function

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel 2003 visual basic intermediate course - The Frequency Function

excel 2003 visual basic intermediate course - The Frequency Function

resolvedResolved · Low Priority · Version Standard

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?

Edited on Fri 30 Nov 2007, 10:55

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.


 

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips


Server loaded in 0.08 secs.