Partial Trendline on Line style Pivot Chart ??

[insanity82007]

hi guys,

I'm wracking my brains out trying to work out how to do this. I'm using Excel 2007.

I have 3.5 years worth of Data on my chart broken down by month on the x axis and I have a revenue per charged labour hour figure ranging from $80 to $150 on the y axis.

When I add a trendline it shows me the trend for the entire period but I want to show a trend for the last 12 months only while still showing the entire 3.5 years by month.

The 2 solutions I have seen around the place are to artificially create a trendline as part of the base dataset or only use a partial dataset (replicate the 2 columns I'm using but only keep the last 12 months of data in them).

When I try the simpler option of adding 2 columns to my base data that only hold the last 12 months of data on them I get a series line that matchest the 3.5 year series line but when I put a trendline against this it assumes I want to start from the beginning of the 3.5 years rather than when the data for the series starts, which is exactly what I DON'T want.

The other option of artificially creating a trendline is proving to be very hard as I'm trying to work out using y = mx + b the slope (m) and the y intercept (b) and when I try doing a test on the full dataset using LINEST, SLOPE and INTERCEPT I'm not getting the same answers as the formula that is displayed on the graph with the "Display Equation on Chart" option ticked under the trendline format menu.

Any ideas???

 

[Andrew Poulsom]

Say your data is in the range A1:B43 with headings in row 1. In C1 enter a header like Trend. In C2:C31 enter the formula =NA(). In C32 enter the formula:

=(SLOPE(B$32:B$43,A$32:A$43)*A32)+INTERCEPT(B$32:B$43,A$32:A$43)

and copy to C33:C43. Then add the Trend column to your pivot table as Sum.

 

[insanity82007]

Say your data is in the range A1:B43 with headings in row 1. In C1 enter a header like Trend. In C2:C31 enter the formula =NA(). In C32 enter the formula:

=(SLOPE(B$32:B$43,A$32:A$43)*A32)+INTERCEPT(B$32:B$43,A$32:A$43)

and copy to C33:C43. Then add the Trend column to your pivot table as Sum.

Thanks for this but it's giving me a trendline above all the points on the chart instead in the middle of the points, which is what the chart trendline would do. Any ideas?

 

[Andrew Poulsom]

Please post some sample data. The solution I posted works for me.

 

[insanity82007]

I ended up using a rolling 12 month option and they we're happier with this option. Thanks anyway.