Trend Line - Equation

I have a chart with a trendline, with the equation for the trendline
displayed in the chart.

How can I either format the equation or reference thecomponents of the
trendline elsewhere in the spreadsheet.

The display says y = 27.894x - 1E+06
and I would like to be able to at least display the second number
differently, and preferably be able to reference thetwo numbers
separately elsewhere in the spreadsheet.

Can this be done (I am using Excel 2000)

Thank you
Rich 80205

Hi Rich,

Your equation is in the form y = mx + c where
m = the gradient
c = Y- Intercept

Use the function SLOPE to return the gradient value and
Use the function INTERCEPT to return the Y-Intercept value

For example if your x values were in A1 to A7 and your y values
were in B1 to B7 then your formula for slope would be

=SLOPE(B1:B7,A1:A7)

HTH
Martin

With an UDF:
=GetEquation("Chart 1", "Y")

Paste the following into a standard module:

'-----------------------------------------
Function getEquation(sChartName As String, sSeriesName As String)
Dim oTrendLine As Trendline
On Error GoTo err_handler
Set oTrendLine = ActiveSheet.ChartObjects(sChartName).Chart _
.SeriesCollection(sSeriesName).Trendlines(1)
getEquation = oTrendLine.DataLabel.Text
Exit Function
err_handler:
getEquation = CVErr(xlErrValue)
End Function
'-------------------------------------------

HTH
--
AP

"Rich 80105" <[email protected]> a écrit dans le message de news:
[email protected]...
>I have a chart with a trendline, with the equation for the trendline
> displayed in the chart.
>
> How can I either format the equation or reference thecomponents of the
> trendline elsewhere in the spreadsheet.
>
> The display says y = 27.894x - 1E+06
> and I would like to be able to at least display the second number
> differently, and preferably be able to reference thetwo numbers
> separately elsewhere in the spreadsheet.
>
> Can this be done (I am using Excel 2000)
>
> Thank you
> Rich 80205

On Sat, 3 Jun 2006 15:18:57 +1000, "MartinW" <[email protected]>
wrote:

>Hi Rich,
>
>Your equation is in the form y = mx + c where
>m = the gradient
>c = Y- Intercept
>
>Use the function SLOPE to return the gradient value and
>Use the function INTERCEPT to return the Y-Intercept value
>
>For example if your x values were in A1 to A7 and your y values
>were in B1 to B7 then your formula for slope would be
>
>=SLOPE(B1:B7,A1:A7)
>
>HTH
>Martin

Thanks, just what I needed
Rich