Calculating a CAGR in Excel 2007 Pivot Table

I'm trying to calculate a compound average growth rate or CAGR in an Excel 2007 pivot table.

I am familiar with the CAGR formula: `((End Value / Start Value)^(1/N)`) -1, where N is the number of periods.

My problem is that I have three pivot table fields: Items, Values and Years. What I want to do is something likes this: ((`Value[Year2012] / Value[Year2002]`) ^(1/10)) -1

Is what I'm trying to do possible the way I have created my pivot table. Thanks for you input.

-

migrated from stackoverflow.comJan 15 '13 at 10:09

This question came from our site for professional and enthusiast programmers.

The `RATE` formula is a superior method to calculate CAGR. In your example, `=RATE(2010-2002,,-10,50)` works out the cagr for an inrcrease from 10 to 50 over eight years – brettdj Jan 15 '13 at 0:58

I don't think it can be done using purely Pivot fields/functionality.

Assuming you always want to get the CAGR from 2002 to 2012, the easiest way to do it with a Pivot is the following:

1. Items in the rows, Years in the columns and value in the data (using the Sum function to aggregate - only if each item/year combination is unique. If not you need to use average or max or min)
2. Filter years to 2002 and 2012
3. Add a formula next to the pivot table to calculate the CAGR for each item based on the values for 2002 and 2012
-