Data Analysis with Microsoft Excel: Updated for Office 2007

134 Fundamentals of Statistics

The table contains fi ve columns. The fi rst column, Price, lists in ascend-

ing order each home price in the sample of 117 homes. Prices in this sample

range from a minimum of $54,000 to a maximum of $215,000. The second

column, Freq, counts the frequency, or number of occurrences, for each

value in the price column. Many prices are unique and have frequencies

of 1, but other prices (such as $75,000) occur for multiple homes. The third

column contains the cumulative frequency, counting the total number of

homes at or less than a given price. By examining the table, you can quickly

see that 24 of the homes in the sample have a price of $75,000 or less. The

fourth column lists the percentage occurrence of each home price out of

the total sample. For example, 1.71% of the homes are listed for exactly

$75,000. Finally, the fi fth and last column of the table calculates the cu-

mulative percentage for the home prices. In this case, 24.79%—almost one-

quarter of the homes—list for $77,300 or less. A table of this kind can help

you in evaluating the market. For example, if you were interested in homes

that list for $125,000 or less, you could quickly determine that almost 80%

of the homes in this database, or 93 different listings, met that criterion.

EXCEL TIPS

If you don’t have StatPlus handy, Excel comes with an add-in

called the Data Analysis ToolPak which you can use to create

a frequency table. The ToolPak does not have all the frequency

table options that StatPlus contains.

If you want to count how many values in a column are equal to a

specifi c value, you can use Excel’s COUNTIF function.

You can also create a frequency table using Excel’s FREQUENCY

function. This function uses Excel’s array feature, which you can

learn about by using the online Help.

Using Bins in a Frequency Table

By creating a frequency table, you got a clear picture of the distribution of prices

in the Albuquerque area back in 1993. However, displaying individual values

would be cumbersome if the sample contained 1,000 or 10,000 observations.

Rather than list individual prices, you can have the frequency table group the

values by placing them in bins, where each bin covers a particular range of val-

ues. The frequency table would then count the number of values that fall in each

bin. There are three ways of counting values in bins as shown in Figure 4-3.

1. Count those values which are $ the bin value and < the next bin value.


2. Count those values which are centered around the bin value (in the case
   of mid-point values, start counting from the lower mid-point).


3. Count those values that are # the bin value but > the previous bin value.

•

•

•