Trend Line - Equation

[Rich 80105]

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

 

[MartinW]

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

 

[Ardus Petus]

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

 

[Rich 80105]

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