0

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.

Improve this question
1
  • 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
    Commented Jan 15, 2013 at 0:58

1 Answer 1

Reset to default
0

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
Improve this answer

You must log in to answer this question.