Built-in Stat functions

[scantor145]

MS Excel 2003

The equation for a linear regression takes the form

y = m*X + b

If you create a chart then add a linear trendline it will display bot
the slope(m) and the y-intercept(b) on the chart. Alternately, withou
creating a chart you can use the built-in statistical functions =SLOPE(
and
=INTERCEPT() to calculate m and b.

The equation for an exponential regression takes the form

y = a*exp (b*x)

Again, I can create a chart and add an exponential trendline producin
the equation showing the values of a and b.

However, I can’t seem to find any built-in statistical functions tha
will yield both a and b.

Do these functions exist

 

[Mike Middleton]

scantor145 -

See the "natural exponential function" section in Tushar Mehta's explanation
at

http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

Tushar's approach uses the array-entered LINEST worksheet function, but you
could use the SLOPE and INTERCEPT worksheet functions.

To find best-fit values of a and b for y = a*EXP(b*x), the usual method is
to transform the data, taking the natural log, i.e., LN(y) = LN(a)+b*x, and
then use linear regression on LN(y) and x, i.e., for the transformed data
the intercept is LN(a) and the slope is b. So LN(a)=INTERCEPT(LN(y),x), and

a = EXP(INTERCEPT(LN(yRange),xRange))
b = SLOPE(LN(yRange),xRange)

These methods find parameters a and b that minimize sum of squared
deviations between actual LN(y) and predicted LN(y).

An alternative, that will produce usually slightly different results, is to
Excel's Solver to search for a and b that minimize sum of squared deviations
between actual y and predicted y.

- Mike
www.mikemiddleton.com

 

[Bernard Liengme]

The function to use is LINEST
LINEST may be used to fit data to other functions:

Function
Expression
Excel formula

Logarithmic
y = aLn(x) + b
=LINEST(y-values, LN(x-values))

Gives a and b

Power
y = axb
=LINEST(LN(y-values), LN(x-values))

Gives Ln(a) and b

Exponential base b
y = abx
=LINEST(LN(y-values), x)

Gives Ln(a) and Ln(b)

Exponential base e
y = aex or

y = aexp(x)
=LINEST(LN(y-values), x)

Gives Ln(a) and b


For an in-depth coverage of the topic see

http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

 

[Bernard Liengme]

In addition see my Tips&Tricks page for info on LOGEST
best wishes