0
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!
Apologies. should have specified it is the growth (i.e. year on year units increase by 133 on average) – BiGXERO – 2012-11-29T21:13:51.763
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 – 2012-12-02T23:09:00.003
From what I understand, if i project the growth rate using a formula with each year in a different column, if i then wanted to report on these caculation using pivot tables/charts, i would then need to alter the data so that it was in a normalised list (e.g. column titles would be 'year', 'units', 'cost') – BiGXERO – 2012-12-02T23:50:17.253
The reason I wanted to go to a pivot system is that the actual data is about 20 columns by 200 rows, needs to be disected in multiple dimensions (by year, by sub department, YoY growth, base growth, etc), and using a pivot system would save me having to manually create all the reports. On a side not, if you put 2nd comment as an answer rather than a comment I can close the thread as that essentially solved my original problem. Again, many thanks for the help in a poorly explained problem – BiGXERO – 2012-12-03T02:29:52.303
Yeah I realised that the pivot table wasn't actually appropriate for what I was trying to achieve. Excel's grouping function and a summary page was enough :P Also I don't know how to delete comments. Id like to upload the excel sheet for further explanation, but again, im not sure how to upload files, and the data is all sensitive. If there's enough interest in the question I may clean & upload the file... – BiGXERO – 2012-12-11T05:22:43.643