Extract slope of a trendline from chart data 1

[JTBorton]

Hey guys, it's been a while. Have an interesting problem that I need some help with. We are graphing some fuel cell data and using the slope to determine internal resistance, R-ohm. I would like to write a macro that will extract this slope from the trendline of the graph. I'm not too familiar with chart objects. I looked through the object browser and found a Chart class (not the ChartObject class) which contained several chart properties and methods. I was unable to find anything to reference the trendline. Also, I'm not entirely sure how to access a chart object on a worksheet. I'm open to any information you guys are willing to offer, and, as always, I am eager to learn.

For your convenience, a portion of the data graphed to obtain the trendline is provided below (excluding the non-linear portion of the graph). This data is not confidential or proprietary.

X Values, Ln(Current Density)
-2.367123614
-2.079441542
-1.85629799
-1.673976434
-1.519825754
-1.386294361
-1.268511325
-1.16315081

Y Values, Cell Potential
1.82
1.76
1.71
1.66
1.62
1.57
1.54
1.5

Trendline: -0.2663x + 1.2042
R^2: 0.991

Thanks guys!

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

Having reference to the chart:
[tt]ChartRef.SeriesCollection(1).Trendlines(1).DataLabel.Caption[/tt]
The string can be analysed to extarct slope. You need the formula displayed on the chart.

combo

 

[mintjulep]

Far easier to work with the data that the chart was made from rather than the chart.

Just use LINEST()

 

[osx99]

These equations assume that your sheet has two named ranges: x and y.

Linear Trendline

Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline

Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline

Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN,LN(x),,),1,2))
b: =INDEX(LINEST(LN,LN(x),,),1)

Exponential Trendline

Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN,x),1,2))
b: =INDEX(LINEST(LN,x),1)

2nd Order Polynomial Trendline

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

 

[SkipVought]

Right off of John Walkenbach's Spreadsheet site.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Wow, all of this has been so helpfull! I've learned a lot. Now I'm still trying to explore accessing the chart objects, just for the sake of learning. I tried Combo's method, but I'm having trouble setting the chart object.

Public Property Get RefSheet() As Worksheet
Dim Wks As Worksheet
    For Each Wks In Worksheets
        If Wks.Name = "Sheet1" Then
            Set RefSheet = Wks
            Exit Property
        End If
    Next Wks
    MsgBox "Worksheet Sheet1 not found"
End Property

Public Function GetTrendline() As String
Dim myChart As Chart
Dim Catch As String
    [highlight]Set myChart = RefSheet.ChartObjects(1)[/highlight] [b][red]Type mismatch[/red][/b]
    Catch = myChart.SeriesCollection(1).Trendlines(1).DataLabel.Caption
End Function

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

[blue]osx99[/blue]

I'm using the =Index(Linest(... method you gave me to solve for polynomial constants, but I'm having trouble retrieving the R^2 value. The function returns C1, C2, C3, and b just fine. Since the R^2 value prints on the 3rd row, 1st column, I tried the following:

Trial Data:
XRange A2:A6
1
2
3
4
5

YRange B2:B6
56
96
64.59
117
-67

=INDEX(LINEST(B2:B6,A2:A6^{1,2,3}),3,1)

Result: #Ref!
Correct R^2: 0.84

Any idea what I may be doing wrong?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

ChartObject is not Chart:
[tt]Set myChart = RefSheet.ChartObjects(1).Chart[/tt]

Concerning error in formula, try:
[tt]=INDEX(LINEST(B2:B6,A2:A6^{1,2,3}),3)[/tt]

I agree that instead of extracting value from string it is better to use formula. It could be more flexible and outside excel interface if WorksheetFunction.Linest was used.

combo

 

[osx99]

SkipVought (Programmer) 21 Oct 09 11:42
Right off of John Walkenbach's Spreadsheet site.

Thanks for pointing that out. I copied my post from a word doc which I must have lifted from his site sometime last year. So credit where it's due. Apologies

-------
Result: #Ref!Correct R^2: 0.84

Any idea what I may be doing wrong

Once you have the 3rd order polynomial constants using the formulas

c3: =INDEX(LINEST(y,x^{1,2,3}),1) = -13.75
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2) = 97.73714286
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3) = -190.9228571
b: =INDEX(LINEST(y,x^{1,2,3}),1,4) = 169.728

Create a new column to work out the baseline for each item in the named range x

eg
1^3 * -13.75 + 1^2*97.73 + 1*-190.92 + 169.728 = 62.7

Do this or each x and then name this range 'benchmark' (or whatever you want)

Then R2 can be calulated in excel using

=RSQ(y,benchmark)

 

[osx99]

this link should make things clearer

 

[DougAJ4]

If you want all the values returned by linest you can enter it as an array function, rather than using the Index function:

Enter the linest function in the top left corner of your output range.
Select the entire output range.
Press F2
Press ctrl-shift enter.

In addition to saving typing, if you need to update the function you just need to edit any one copy in the output range and press ctrl-shift-enter, and the edit is copied to all the other cells in the range.


BTW, if I ever knew about the SLOPE and INTERCEPT functions I had forgotten about them, so thanks osx99 (and John Walkenbach).