Predicting an Intersection of a Linear Trend

[Idgarad]

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)

 

[Jerry W. Lewis]

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]

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











- Show quoted text -

I can follow to a degree what you are saying but how do I translate
that into an excel formula?

 

[Jerry W. Lewis]

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]

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






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