XL: Creating a Frequency Distribution Chart w/FREQUENCY()

• Microsoft Excel for Windows, versions 4.0, 4.0a
• Microsoft Excel for the Macintosh, version 4.0

SUMMARY

To create a frequency distribution chart using the FREQUENCY() function are as follows, you need to do the following:

• Determine the intervals or categories for your bin values.



• Use the FREQUENCY() function to determine frequency of your data.



• Divide the results for each bin value by the number of values in the data_array argument and display these results as a percentage of each bin value.



• Create a chart and chart overlay using the value and percent value for each bin.

Example

1. Enter the following data for your the data_array values:
   
   

   
          A1:  2
          A2:  5
          A3:  8
          A4: 11
          A5: 12
          A6: 19
          A7: 21
          A8: 32
          A9: 45
         A10: 48 




2. Enter the the following values for your bins_array values:
   
   

   
          B1: 10
          B2: 20
          B3: 30
          B4: 40 




3. Select the range C1:C5 and enter the following formula:
   
   =FREQUENCY(A1:A10,B1:B4)/10
   
   You must enter this formula as an array by pressing CTRL+SHIFT+ENTER (Windows) or COMMAND+ENTER (Macintosh).
   
   NOTE: Before you enter the FREQUENCY() function, you always have to select one cell more than the number of values in the bins array. This example uses four values in the bins_array, so you must select five cells before entering the function.
   
   The resulting values are: .3, .3, .1, .1, and .2
   
   The function entered was divided by the number of values in data_array (10 in this example) to display the values in the resulting array as percentages of each bin value.



4. Select the resulting values and choose Number from the Format menu. Select one of the percent number format codes and choose OK. The resulting values to be charted are: 30%, 30%, 10%, 10%, and 20%.

1. Select the bin range (B1:B4).



2. Press and hold down the CTRL key (Windows) or the COMMAND key (Macintosh) and select the percentages (C1:C5).



3. From the File menu, choose New, choose Chart and choose OK. With First Data Series selected in the New Chart dialog box, choose OK.



4. In the new chart, do the following:
   
   a. From the Chart menu, choose Add Overlay.
   
   b. From the Chart menu, choose Axes and select the Value (Y) Axis check box under Overlay.

MORE INFORMATION

The FREQUENCY() function returns the number (count) of elements in the data_array argument contained in the intervals indicated in bins_array.

Additional query words: distribution cumulative

Keywords          : 
Version           : WINDOWS: 4.0,4.0a; MACINTOSH: 4.0
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto 

Last Reviewed: April 7, 1999