Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Predicting an Intersection of a Linear Trend
Here is the problem as it stands:
First some sample data: 02/05/07 88.9% 02/12/07 80.6% 02/19/07 85.8% 02/26/07 84.4% 03/05/07 93.0% 03/12/07 90.6% 03/19/07 96.3% 03/26/07 87.0% 04/02/07 90.4% 04/09/07 91.0% 04/16/07 84.7% 04/23/07 91.7% What Needs to be found: Based on the trend when will the utilization go above 95% based on a linear growth trend. Why: I need to graph a bar indicating when that 95% mark is met (for capacity planning.) How did this come about: Normally I throw on a trendline and manually move a marker. I would rather, for the sake of accuracy have the indicator plotted. Any suggestions? All I need back is the date this happens (I'll be setting the bar to 100% manually as to make a wall) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Predicting an Intersection of a Linear Trend
If y=intercept+slope*x, then x=(y-intercept)/slope
Simply calculate x for y=0.95 (95%). Slope and intercept can be obtained from the worksheet SLOPE and INTERCEPT functions, from the LINEST worksheet function, or from the chart trendline (formatted to show enough figures for the equation). However, a plot of the data suggests the possibility that the utilization slope (if really linear) may have decreased near the end of March. Jerry "Idgarad" wrote: Here is the problem as it stands: First some sample data: 02/05/07 88.9% 02/12/07 80.6% 02/19/07 85.8% 02/26/07 84.4% 03/05/07 93.0% 03/12/07 90.6% 03/19/07 96.3% 03/26/07 87.0% 04/02/07 90.4% 04/09/07 91.0% 04/16/07 84.7% 04/23/07 91.7% What Needs to be found: Based on the trend when will the utilization go above 95% based on a linear growth trend. Why: I need to graph a bar indicating when that 95% mark is met (for capacity planning.) How did this come about: Normally I throw on a trendline and manually move a marker. I would rather, for the sake of accuracy have the indicator plotted. Any suggestions? All I need back is the date this happens (I'll be setting the bar to 100% manually as to make a wall) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Predicting an Intersection of a Linear Trend
On May 15, 2:34 pm, Jerry W. Lewis wrote:
If y=intercept+slope*x, then x=(y-intercept)/slope Simply calculate x for y=0.95 (95%). Slope and intercept can be obtained from the worksheet SLOPE and INTERCEPT functions, from the LINEST worksheet function, or from the chart trendline (formatted to show enough figures for the equation). However, a plot of the data suggests the possibility that the utilization slope (if really linear) may have decreased near the end of March. Jerry "Idgarad" wrote: Here is the problem as it stands: First some sample data: 02/05/07 88.9% 02/12/07 80.6% 02/19/07 85.8% 02/26/07 84.4% 03/05/07 93.0% 03/12/07 90.6% 03/19/07 96.3% 03/26/07 87.0% 04/02/07 90.4% 04/09/07 91.0% 04/16/07 84.7% 04/23/07 91.7% What Needs to be found: Based on the trend when will the utilization go above 95% based on a linear growth trend. Why: I need to graph a bar indicating when that 95% mark is met (for capacity planning.) How did this come about: Normally I throw on a trendline and manually move a marker. I would rather, for the sake of accuracy have the indicator plotted. Any suggestions? All I need back is the date this happens (I'll be setting the bar to 100% manually as to make a wall)- Hide quoted text - - Show quoted text - I can follow to a degree what you are saying but how do I translate that into an excel formula? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Predicting an Intersection of a Linear Trend
Assuming that the fit should be linear (which is questionable), you simply
use the formula from my previous post, i.e. =(95%-INTERCEPT(utilization_data,date_data))/SLOPE(utilization_data,date_data) where utilization_data and date_data point to the locations for the corresponding data. Jerry "Idgarad" wrote: On May 15, 2:34 pm, Jerry W. Lewis wrote: If y=intercept+slope*x, then x=(y-intercept)/slope Simply calculate x for y=0.95 (95%). Slope and intercept can be obtained from the worksheet SLOPE and INTERCEPT functions, from the LINEST worksheet function, or from the chart trendline (formatted to show enough figures for the equation). However, a plot of the data suggests the possibility that the utilization slope (if really linear) may have decreased near the end of March. Jerry "Idgarad" wrote: Here is the problem as it stands: First some sample data: 02/05/07 88.9% 02/12/07 80.6% 02/19/07 85.8% 02/26/07 84.4% 03/05/07 93.0% 03/12/07 90.6% 03/19/07 96.3% 03/26/07 87.0% 04/02/07 90.4% 04/09/07 91.0% 04/16/07 84.7% 04/23/07 91.7% What Needs to be found: Based on the trend when will the utilization go above 95% based on a linear growth trend. Why: I need to graph a bar indicating when that 95% mark is met (for capacity planning.) How did this come about: Normally I throw on a trendline and manually move a marker. I would rather, for the sake of accuracy have the indicator plotted. Any suggestions? All I need back is the date this happens (I'll be setting the bar to 100% manually as to make a wall)- Hide quoted text - - Show quoted text - I can follow to a degree what you are saying but how do I translate that into an excel formula? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Predicting an Intersection of a Linear Trend
On May 21, 11:39 am, Jerry W. Lewis
wrote: Assuming that the fit should be linear (which is questionable), you simply use the formula from my previous post, i.e. =(95%-INTERCEPT(utilization_data,date_data))/SLOPE(utilization_data,date_da*ta) where utilization_data and date_data point to the locations for the corresponding data. Jerry "Idgarad" wrote: On May 15, 2:34 pm, Jerry W. Lewis wrote: If y=intercept+slope*x, then x=(y-intercept)/slope Simply calculate x for y=0.95 (95%). Slope and intercept can be obtained from the worksheet SLOPE and INTERCEPT functions, from the LINEST worksheet function, or from the chart trendline (formatted to show enough figures for the equation). However, a plot of the data suggests the possibility that the utilization slope (if really linear) may have decreased near the end of March. Jerry "Idgarad" wrote: Here is the problem as it stands: First some sample data: 02/05/07 88.9% 02/12/07 80.6% 02/19/07 85.8% 02/26/07 84.4% 03/05/07 93.0% 03/12/07 90.6% 03/19/07 96.3% 03/26/07 87.0% 04/02/07 90.4% 04/09/07 91.0% 04/16/07 84.7% 04/23/07 91.7% What Needs to be found: Based on the trend when will the utilization go above 95% based on a linear growth trend. Why: I need to graph a bar indicating when that 95% mark is met (for capacity planning.) How did this come about: Normally I throw on a trendline and manually move a marker. I would rather, for the sake of accuracy have the indicator plotted. Any suggestions? All I need back is the date this happens (I'll be setting the bar to 100% manually as to make a wall)- Hide quoted text - - Show quoted text - I can follow to a degree what you are saying but how do I translate that into an excel formula?- Hide quoted text - - Show quoted text - I got it working, that was a great help. If a linear projection isn't ideal do you have any reccomendations given the available data and the goal of having a good accurate projection of when the CPU utilization is being exceeded? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula that autofills by trend rather than linear | Excel Discussion (Misc queries) | |||
A formula that autofills by trend, not linear | Excel Discussion (Misc queries) | |||
Linear Regression using the TREND function | Excel Worksheet Functions | |||
How do I anchor one end of a linear trend line? | Charts and Charting in Excel | |||
intersection with linear range as true argument | Excel Programming |