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 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!

share|improve this question
    
Is 133 'a straight line average of the previous years' units? –  pnuts Nov 29 '12 at 13:08
    
Apologies. should have specified it is the growth (i.e. year on year units increase by 133 on average) –  BiGXERO Nov 29 '12 at 21:13
    
As you mention 'never used them before' dare I suggest (wonderful as PTs are!) this may not be the best approach in this instance? I'd build the projections before pivotting them - so it is easier to see what is going on and you have the flexibility to change the growth projections by any amount for any year and any metric. I would rearrange the source so each year is a different column and use a formula (with +133 or +166) to fill in 2012 and subsequent years. –  pnuts Dec 1 '12 at 10:45
    
Thanks for the heads up. So Do you then find that its a problem normalising the data after you have created the worksheet in a matrix formation? –  BiGXERO Dec 2 '12 at 23:09
    
Sorry, don't understand requirement for normalisation! Maybe you could show the values you require for 2012 and 2013 (two each year)? -or has the penny just dropped, individual units could be +/- anything, but IN TOTAL +133? –  pnuts Dec 2 '12 at 23:11
show 6 more comments

1 Answer

up vote 0 down vote accepted

As you mention 'never used them before' dare I suggest (wonderful as PTs are!) this may not be the best approach in this instance? I'd build the projections before pivotting them - so it is easier to see what is going on and you have the flexibility to change the growth projections by any amount for any year and any metric. I would rearrange the source so each year is a different column and use a formula (with +133 or +166) to fill in 2012 and subsequent years.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.