insanity82007
Board Regular
- Joined
- Oct 10, 2007
- Messages
- 130
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???
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???