Use the FREQUENCY function to create histograms that update with your data
Use the FREQUENCY function to create histograms that update with your data
by Sean Kavanagh
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
Download:
http://download.elijournals.com/excel/201003/frequency.zip
The Analysis ToolPak add-in that comes with Excel provides the Histogram analysis tool, which is extremely helpful for quickly creating frequency distribution tables and histogram charts. However, there’s a drawback to using this tool: The results are static. Fortunately, you can create a dynamic histogram by basing the chart on formula results.
To create a histogram that updates when your data does, we’ll:
• Set up a worksheet that contains a small dataset.
• Examine the FREQUENCY function’s syntax and use the function to create a frequency distribution table.
• Base a column chart on the table and then examine the impact that changing data has on the histogram.
Determining the number of times data items fall in certain value ranges, which is what a histogram accomplishes, can reveal more about a dataset than examining and charting all of the individual values. However, changes you make in the histogram’s input and bin ranges aren’t reflected in the histogram. Instead, you’re forced to re-create the histogram each time the data changes. In this article, we’ll show you how to use the FREQUENCY function to create a frequency distribution table that can serve as a histogram’s data source.
See if students are making the grade
To demonstrate how to create a frequency distribution table with worksheet functions, we’ll start with a set of numeric student grades. We’ll use a FREQUENCY formula to count how many grades fall into a set of bins that correspond to letter grades.
Download: You can extract the starting data from the file frequency.zip found at the URL shown at the beginning of this article, or follow along to set up the worksheet.
To set up the example:
1. Add the data shown in Figure A to a blank worksheet.
2. Select cell E2 and enter the formula =ROUND(AVERAGE
(B2:D2),0) to produce the average values shown in column E.
(B2:D2),0) to produce the average values shown in column E.
3. Select cell E2 and click and drag the Fill handle to copy the formula to cells E3:E20.
A:
If you perform a frequency distribution analysis of this data with the Histogram tool, the histogram won’t update when you enter the Test 3 results.
Construct a frequency distribution table
The FREQUENCY function’s syntax is simply as follows:
FREQUENCY(data_array,bins_array)
The function returns a column of frequency values, so it must be entered as an array function. That means you need to pre-select th[...]
