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 cumu-
lative 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 previous item.
2. Set up a column where the first entry is 100, and successive entries increase by the percentage increases
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-14, involves rentals per square foot of different types of space between 2006
and 2012. 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 third table.
This example is available at this book's website in the workbook indices.xlsx.
The formulas for calculating the growth rates (in the second table) are simple. For example, the formula in cell