Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.