Capture values for polynomial trend line

[Mumfy]

Does anyone know how I can capture the VALUES when I generate a polynomial trend line on a chart using the ADD TREND LINE facility in MS Excel? I need to do this as I want to compare some of my data values to the trend line values so I can do further analysis on my data values.

I would greatly appreciate any help you can give me on this.

Thanks in advance, Mumfy.

 

[macropod]

Hi Mumfy,

It's probably better to extract the trendline formula, which you can do with:

Sub GetTrendFormula()
Dim EqState
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
    EqState = .DisplayEquation
    .DisplayEquation = True
    ActiveSheet.Cells(5, 5).Value = .DataLabel.Text
    .DisplayEquation = EqState
End With
End Sub

The above code outputs the trendline formula in E5. Change to suit your requirements. You can then easily use the formula to generate the x values from the known y values in your data.

Cheers


[MS MVP - Word]

 

[macropod]

Hi Mumfy,

Here's an enhanced version - it puts in the power symbols, where appropriate, so that instead of getting something like
y = 2.048x2 - 3.096x3 + 1.5x + 3
you'll get
y = 2.048x^2 - 3.096x^3 + 1.5x + 3:

Sub GetTrendFormula()
Dim EqState
Dim i As Integer
Dim TmpStr As String
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
    EqState = .DisplayEquation
    .DisplayEquation = True
    TmpStr = .DataLabel.Text
    .DisplayEquation = EqState
    For i = Len(TmpStr) - 1 To 1 Step -1
        If Mid(TmpStr, i, 1) = "x" And IsNumeric(Mid(TmpStr, i + 1, 1)) Then _
            TmpStr = Mid(TmpStr, 1, i - 1) & Replace(TmpStr, "x", "x^", i, 1, vbTextCompare)
    Next
    ActiveSheet.Cells(5, 5).Value = TmpStr
End With
End Sub

Cheers

[MS MVP - Word]

 

[Mumfy]

Hi macropod,

Thanks for your prompt responses. The second one worked very well. If I could ask now how to fully use this formula I would greatly appreciate you answer and your time.

The formula looks like this:

y = 4E-13x^6 - 1E-07x^5 + 0.0094x^4 - 491.82x^3 + 1E+07x^2 - 2E+11x + 1E+15

You said in your first response "You can then easily use the formula to generate the x values from the known y values in your data."

How do I do this? Yes, I do know the y values for each observation of my data and what is the E value that I should use?

Thanks in anticipation for your help.

Mumfy.

 

[macropod]

Hi Mumfy,

Assuming your data start in A1, the formula:

y = 4E-13x^6 - 1E-07x^5 + 0.0094x^4 - 491.82x^3 + 1E+07x^2 - 2E+11x + 1E+15

you delete the 'y' and change all the 'x's to '*A1's so the formula becomes:

= 4E-13*A1^6 - 1E-07*A1^5 + 0.0094*A1^4 - 491.82*A1^3 + 1E+07*A1^2 - 2E+11*A1 + 1E+15

As soon as you hit enter after doing this, Excel converts it to:

= 0.0000000000004*A1^6 - 0.0000001*A1^5 + 0.0094*A1^4 - 491.82*A1^3 + 10000000*A1^2 - 200000000000*A1 + 1000000000000000

The results of the calculation might display in scientific notation by default, but you can change that.

Cheers

[MS MVP - Word]

 

[combo]

You could create custom function:

Function TrendValue(TrendLineFormula As String, PointValue As Double) As Double
Dim TmpStr As String
TmpStr = TrendLineFormula
' convert string to proper formula
TmpStr = Replace(TmpStr, "y", "")
For i = Len(TmpStr) - 1 To 1 Step -1
    If Mid(TmpStr, i, 1) = "x" And IsNumeric(Mid(TmpStr, i + 1, 1)) Then _
        TmpStr = Mid(TmpStr, 1, i - 1) & Replace(TmpStr, "x", "x^", i, 1, vbTextCompare)
Next
TmpStr = Replace(TmpStr, " ", "")
' complete with input value and local decimal separator
TmpStr = Replace(TmpStr, "x", "*(" & PointValue & ")")
TmpStr = Replace(TmpStr, Application.DecimalSeparator, ".")
' evaluate value
TrendValue = Application.Evaluate(TmpStr)
End Function

It can be used as regular function in excel, with first argument containing or pointing to raw formula string, second pointing to or containing input value.


combo

 

[Mumfy]

Hi Guys (macropod & combo)
Thanks a lot for your help. I will end up using ideas from both of your suggestions. I would never have worked this out as quickly myself. Great work.

Thanks Mumfy.