Microsoft Office Tutorials and References
In Depth Information
The final topic in this chapter demonstrates how to create an index from schedules of changing
values. An index is commonly used to compare how data changes over time. An index allows
easy cross-comparison between different periods and between different data sets.
For example, consumer price changes are recorded in an index in which the initial “shopping
basket” is set to an index of 100. All subsequent changes are made relative to that base. Therefore,
any two points show the cumulative effect of increases.
Using indices makes it easier to compare data that use vastly different scales — such as
comparing a consumer price index with a wage index.
Perhaps the best approach is to use a two-step illustration:
1. Convert the second and subsequent data in the series to percentage increases from the
2. Set up a column where the first entry is 100 and successive entries increase by the
percentage increases previously determined.
Although a two-step approach is not required, a major advantage is that the calculation of the
percentage changes is often very useful data in its own right.
The example, shown in Figure 13-15, involves rentals per square foot of different types of space
between 2003 and 2009. The raw data is contained in the first table. This data is converted to
percentage changes in the second table, and this information is used to create the indices in the
This example is available on the companion CD-ROM in the workbook indices.xlsx .
The formulas for calculating the growth rates (in the second table) are simple. For example, the
formula in cell C14 is as follows:
This formula returns –0.92%, which represents the change in retail space (from $89 to $88). This
formula is copied to the other cells in the table (range C14:H18). This information is useful, but it is
difficult to track overall performance between periods of more than a year. That’s why indices are