Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 11

Frequency Distributions

In this chapter you will find out how to generate frequency distributions and frequency distribution charts from a data set. Frequency distributions enable you to better understand the data and the way it is arranged rather than just looking at the data or the statistics. Frequency distribution allows you to see if the data is symmetric, skewed, or flat and detect other information that the statistics could provide.

I will use the same data used in Chapter 10; you will find the data in the Excel file called Chapter 11. I calculated various statistics in Chapter 10 for that same data. Two important values were found that will help in determining the frequency distribution range: the lowest and the highest values. The lowest value is 61 and 129 is the highest. See Figure 11.1.

FIGURE 11.1 Frequency Bins Are in F2:F8

The first step is to create a list of bins in column F, which will be used to establish the boundaries for the groups I wish to form for the frequency. In other words, each one of these bins represents the upper limit of the group’s frequency occurrence. Since I established that the lowest number, or MIN, is 61 and the highest number is 129, I created bins that will encompass this range. I used 70 as the first bin in order to count all of the numbers/values below 70 (and above 61 the lowest number). I used 130 as the upper limit of ...