Trendline Data from Charts

[Andy]

Hi all
Does anybody know if it is possible to capture the data contained in a
trendline legend using VBA. Specifically I am interested in the getting the
base and exponent values of the y function. Currently I have a series of
input boxes that prompt the user to enter the base and exponent values
respectively (as they can see them on the chart). However, it would be cool
if I could get the code to do all this. Can anybody help please?!
Thanks
Andy

[Andy]

Please ignore this question - I've managed to work out a solution based on
previous posts requesting the same for polynomial trendlines.

"Andy" wrote:

> Hi all
> Does anybody know if it is possible to capture the data contained in a
> trendline legend using VBA. Specifically I am interested in the getting the
> base and exponent values of the y function. Currently I have a series of
> input boxes that prompt the user to enter the base and exponent values
> respectively (as they can see them on the chart). However, it would be cool
> if I could get the code to do all this. Can anybody help please?!
> Thanks
> Andy

[mikerr]

Even though the original poster found his own way, I would still find it helpful if someone could still answer this for me. Thanks.

[STEVE BELL]

Mike,

Excel has a number of built-in statistical worksheet functions that you may
be able to use
(even in code).

I believe the Solver Add-In also has a number of statistical functions.

And than you can build your own functions either on the worksheet or with
code.

I always remember the old formula for a straight line:
y = mx + b

If you can find a similar formula for your chart than you can use the data
range
to get all the stats you want...


--
steveB

Remove "AYN" from email to respond
"mikerr" <[email protected]> wrote in
message news:[email protected]...
>
> Even though the original poster found his own way, I would still find it
> helpful if someone could still answer this for me. Thanks.
>
>
> --
> mikerr
> ------------------------------------------------------------------------
> mikerr's Profile:
> http://www.excelforum.com/member.php...o&userid=25830
> View this thread: http://www.excelforum.com/showthread...hreadid=392109
>

[Andy]

Hi Mike
I hope you're still checking this!
I think the code you will need to use varies depending on what type of
trendline you are using. In my case it was a power line (my code is below
and comes with a health warning!!). In effect it returns an array where the
first element is the base number, the second element is the exponent and the
third element is the R^2 value. The inputs to the function are simply a
chart and a number identifying the index of the series (in case you have more
than one - I use an enum). As the comments state this works by extracting
the data from the trendline data label. If you want to get the information
straight from the source data then have a look here:

http://www.j-walk.com/ss/excel/tips/tip101.htm

Also try doing a search on this newsgroup for 'trendline data' as there have
been a few other related queries, which is how I figured this out.

Good luck!

Private Function GetPowerTrendData(myChart As Chart, stSeries As
SERIES_TYPE) As Variant
'This function strips out the trendline data from the label
'and returns them in an array. It only works for a power trend
'curve.
Dim serData As Series
Dim tl As Trendline
Dim intPos As Integer
Dim strText As String
Dim varArr As Variant

'Assign the series and trendline variables
Set serData = myChart.SeriesCollection(stSeries)
Set tl = serData.Trendlines(1)

'Only proceed if the trendline type is a power curve
If tl.Type = xlPower Then

'Get the text of the trendline label
strText = tl.DataLabel.Text

'Find the first instance of an "=" symbol (will not need the "y=" bit
intPos = InStr(1, strText, "=")

'Replace the 'x' with a delimiter for the Split function
strText = Replace(strText, "x", "|")

'Replace the return char and R2 text with a delimiter
strText = Replace(strText, Chr(10) & "R2 = ", "|")

'Replace the text string with the end part (excluding the y=)
strText = Mid(strText, intPos + 1)

'Split the text, assign to an array and return the array
varArr = Split(strText, "|")
GetPowerTrendData = varArr
Else
MsgBox "This function can only be used for a Power trendline.",
vbExclamation, "Incorrect Trendline"
End If


End Function



"mikerr" wrote:

>
> Even though the original poster found his own way, I would still find it
> helpful if someone could still answer this for me. Thanks.
>
>
> --
> mikerr
> ------------------------------------------------------------------------
> mikerr's Profile: http://www.excelforum.com/member.php...o&userid=25830
> View this thread: http://www.excelforum.com/showthread...hreadid=392109
>
>

[mikerr]

That's what I was hoping for. Thanks!

[David J. Braden]

Mike---
You might be interested in code that I posted some time back; link (allow
for wrap) is
http://groups-beta.google.com/group/...440d271303e0d6

If you Google under the newsgroups for microsoft.public.excel.*, author =
Braden, words contain "trendline", you can get a sense of community effort
and thought leading to the code. You *will* need to watch out for line
breaks that happened with the posted code. Sorry about that. I also posted
code back in 1999 to extract the numbers, but the later post (2003) works
more as a function. It is intended to get around then-extant problems with
LINEST; I don't yet know if these problems have been fixed with the Office
SP1 update, but given Microsoft's response time to fixing other problems
with Excel, I sorta doubt Linest is working well.

HTH
Dave Braden


"mikerr" <[email protected]> wrote in
message news:[email protected]...
>
> That's what I was hoping for. Thanks!
>
>
> --
> mikerr
> ------------------------------------------------------------------------
> mikerr's Profile:
> http://www.excelforum.com/member.php...o&userid=25830
> View this thread: http://www.excelforum.com/showthread...hreadid=392109
>