Creating a Frequency Distribution


Excel provides a number of ways to create a frequency distribution, but none of those methods is easier than using a pivot table. Figure 18-26 shows part of a table of 221 students and the test score for each. The goal is to determine how many students are in each ten-point range (1–10, 11–20, and so on).

image from book
Figure 18-26: Creating a frequency distribution for these test scores is simple.

On the CD 

This workbook, named image from book test scores.xlsx, is available on the companion CD-ROM.

The pivot table is simple:

  • The Score field is in the Row Labels section (grouped).

  • Another instance of the Score field is in the Values section (summarized by Count).

The Grouping dialog box that generated the bins specified that the groups start at 1, end at 100, and are incremented by 10.

Note 

By default, Excel does not display items with a zero value. In this example, no test scores are below 21, so the 1–10 and 11–20 items are hidden. To override this setting, access the PivotTable Options dialog box, click the Display tab, and put a check mark next to Display Item Labels When No Fields Are in the Values Area.

Figure 18-27 show the frequency distribution of the test scores, along with a pivot chart, created by choosing PivotTable Tools image from book Options image from book Tools image from book PivotChart.

image from book
Figure 18-27: The pivot table and pivot chart shows the frequency distribution for the test scores.

Note 

This example used Excel's Grouping dialog box to create the groups automatically. If you don't want to group in equal-sized bins, you can create your own groups. For example, you may want to assign letter grades based on the test score. Select the rows for the first group and then choose Group from the shortcut menu. Repeat these steps for each additional group. Then replace the default group names with more meaningful names.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net