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 both the slope(m) and the y-intercept(b) on the chart. Alternately, without 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 producing the equation showing the values of a and b.

However, I can’t seem to find any built-in statistical functions that 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/ti...efficients.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


"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> 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 both
> the slope(m) and the y-intercept(b) on the chart. Alternately, without
> 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 producing
> the equation showing the values of a and b.
>
> However, I can't seem to find any built-in statistical functions that
> will yield both a and b.
>
> Do these functions exist?
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
> http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=505346
>

[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/ti...efficients.htm

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> 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 both
> the slope(m) and the y-intercept(b) on the chart. Alternately, without
> 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 producing
> the equation showing the values of a and b.
>
> However, I can’t seem to find any built-in statistical functions that
> will yield both a and b.
>
> Do these functions exist?
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
> http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=505346
>

[Bernard Liengme]

In addition see my Tips&Tricks page for info on LOGEST
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"scantor145" <[email protected]> wrote
in message news:[email protected]...
>
> 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 both
> the slope(m) and the y-intercept(b) on the chart. Alternately, without
> 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 producing
> the equation showing the values of a and b.
>
> However, I can’t seem to find any built-in statistical functions that
> will yield both a and b.
>
> Do these functions exist?
>
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile:
> http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=505346
>