Predicting an Intersection of a Linear Trend

I

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)
 
J

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
 
I

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

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
 
I

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top