Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Jan 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
share|improve this answer

You must log in to answer this question.