Trendline Equation

B

bastien86

Is there a way to insert the equation of my trendline into calculations in my
worksheet. The rate is always changing each passing day due to more
collected data... I wish to give a daily projection on when the certain
maximum is going to be reached, which is goign to always be changing because
of the changing trendline. Can I make reference to a data sereis trendline
ie.

y=10e^5x

x = hours

is there a way i can calculate x for a set y? and if the trendline changes,
then so does the value of x?
 
B

bastien86

thats a good site, however I do not have an inital value. i can't get the
value of B in the equation y=Be^Ax to equal the B in the trendline. but i
can get A with with the logest formula. Any tips on getting the value of B?
 
M

Mike Middleton

bastien86 -

For a solution using worksheet functions, to get the coefficients for
Excel's exponential trendline function with form y = c*EXP(b*x),

c = EXP(INTERCEPT(LN(yrange),xrange))
b = SLOPE(LN(yrange),xrange)

For a specific yvalue, the corresponding x on the fitted curve is

x = (LN(yvalue)-LN(c))/b

So, if you put the formulas for c and b in worksheet cells, and if the
formula for x has cell references to those cells and to a cell where you
specify a yvalue, then c and b and x will update dynamically when you
respecify the yrange or xrange.

- Mike
www.mikemiddleton.com
 
B

bastien86

My graph is displaying source data ='E3 Live '!$AK$1037:$AK$2168 and the
trendlines equation is y=.6954e^(-0.0001x). In my excel the formula
"=EXP(INTERCEPT(LN(AK1037:AK2168),A1037:A2168))" result in 0.802566202,
shouldn't it result in 0.6954???
 
M

Mike Middleton

bastien86 -

I do not have your worksheet data or your chart. But here is a possible
explanation:

If you are using an Excel XY (Scatter) chart type, then both the chart axes
and the trendline will use the numerical values of the data ranges.

If you are using an Excel Line chart type, then the x values are only being
used as labels for the horizontal axis and the trendline is using 1,2,3,...
as the x values.

Use an XY (Scatter) chart type.

- Mike
www.mikemiddleton.com
 
Top