Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
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 in time show the cumulative effect of increases.
| Tip | 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:
-
Convert the second and subsequent data in the series to percentage increases from the previous item.
-
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 2000 and 2006. 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.
| On the CD | This example is available on the companion CD-ROM in the workbook |
The formulas for calculating the growth rates (in the second table) are simple. For example, the formula in cell C14 is as follows:
=(C5-B5)/B5
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 required.
Calculating the indices in the third table is also straightforward. The 2000 index is set at 100 (column B) and is the base for the indices. The formula in cell C23 is
=B23*(1+C14)
This formula is copied to the other cells in the table (range C23:H27).
These indices make it possible to compare performance of, say, offices between any two years, and to track the relative performance over any two years of any two types of property. So it is clear, for example, that retail property rental grew faster than office rentals between 2000 and 2006.
The average figures (column I) are calculated by using the RATE function. This results in an annual growth rate over the entire period.
The formula in I23 that calculates the average growth rate over the term is
=RATE(6,0,B23,-H23,0)
The nper argument is 6 in the formula because that is the number of years since the base date.
Категории