I currently have the following (simplified) raw data:
Metric Value Year Units 500 2009 Cost 1200 2009 Units 600 2010 Cost 1500 2010 Units 1000 2011 Cost 1600 2011
In a pivot table like:
2009 2010 2011 Units 500 600 1000 Cost 1200 1500 1600
I want to be able to project these figures 10 years into the future, based off a straight line average of the previous years (i.e each year units would increase by 133 and cost by 166). What is the best way to do this within the constructs of pivot tables (never used them before)
Here's a few options ive considered:
- Calculate growth in the field table : Not sure how to do this, my current attempts mean that 2012 is a duplicate column next to each of the previous years
- Create a separate sheet for calculations: Copy the data from the pivot table into a sheet, calculate the growth figures, normalise them into the same table as the raw data, then update the pivot table (as you can see, im trying to avoid this option).
So essentially my issues are where and how is it best to calculate the growth. Have googled the topic to surprisingly little yield. Any help vastly appreciated!