Excel8; macro to get trendline equation of graph in a cell

[M.J.M. Maes]

Hi there,

I hope someone can help me with this (bull: I wouldn't ask if I didn't
hope:-)

When making a scatter-diagram using some pairs of X-Y numbers you can ask
Excel to write the equation of the trendline in the graph.
You can choose between 5 types of trendlines and their equations (linear,
polynomial, LN, e-power or x-power)
This equation then is depicted in a textframe within the graph frame.

This "equation-text" can be copied into a cell by performing the following
steps.
1... First select the equation-frame in the graph,
2... Next select the text itself,
3... Then copy this text into a cell
This results in a text-string in the cell.

When done "by hand" the operation is performed without problem.
So I thought that it also would be easy to set up a macro simply by
recording the steps
as described above.
But this does not work. I think (I'm not a VBA expert yet) it is because
this text is not an available object in VBA so the problem may be of a more
fundamental kind than just my poor knowledge of VBA.
Or am I wrong?

By the way:
The "end-goal" of this is to transform the text in the picture to an Excel
formula. Then one can use this formula for instance to find other Y-values
or to calculate a tangent line in a given point.
The original text can be transformed to a text which looks like a normal
worksheet formula, basically by substituting the character "x" by a
"A1" cell reference.
It is not quiet that simple but with some patience I have transformed each
one of the 5 possible types. (using only about 30 worksheet formulae so
there is no VBA code involved yet, there are enough cells in a workbook).
Send an e-mail to this group or to my personal address if you're interested
in the "so far"-sheet.

Greetings from Holland,

Marcel

[Conrad Carlberg]

Hi Marcel,

Given your goal as stated, you can use the worksheet function LINEST()
to get the regression coefficients. While I've not been privileged to
see the actual code behind the creation of chart trendlines, it's
pretty clear that Excel uses LINEST's algorithm to calculate the
trendline equation for the chart.

There are several advantages to using LINEST instead of copying the
formula from the chart. One is that you avoid the problems with
truncated coefficients, necessary so that the equation doesn't crowd
the chart. Another is that it's easy to get new coefficients as you
add to or otherwise change the source data.

For example, suppose that you've charted the data in A1:B20 and
obtained a 3rd order polynomial trendline equation from the chart. You
can get the coefficients by means of:

=LINEST(B1:B20,A1:A20^{1,2,3},,TRUE)

where B1:B20 contains the y-values, A1:A20 contains the x-values,
{1,2,3} indicates that the x-values are to be raised to the 1st, 2nd
and 3rd powers, and the TRUE indicates that Excel is to return the
full set of regression statistics.

You need to array-enter this formula, by using Ctrl-Shift-Enter
instead of just Enter. Before typing the formula, select a blank range
5 rows high and 4 columns wide (3 columns for the coefficients, 1 for
the constant). Once it's array-entered, the first row will display the
coefficients.

As to your question about automating the process of copying and using
the trendline equation, I don't understand why it didn't work for you.
Just for grins I tried recording the process and this is what the
recorder produced (and it executes just fine for me; perhaps you chose
a more complex equation):

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
ActiveWindow.Visible = False
Windows("Book1").Activate
Range("H1").Select
ActiveSheet.Paste
Cells.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
ActiveCell.Replace What:="x", Replacement:="*B1", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False

Of course, that's just my opinion. I could be wrong.

C^2
Conrad Carlberg
Microsoft MVP -- Excel

[M.J.M. Maes]

Hey Conrad,

I'll try the LINEST function as well as your macro.
I thought that LINEST only performs a linear regression, so a regression
that yields y=mx+n. Obviously it also works for polynomials.
But does it also work for LN, e-power and x-power equations?

Anyway, I learned again something new about this great program so....

Thanks a lot for your answer.

Marcel

[Conrad Carlberg]

Marcel,

>I'll try the LINEST function as well as your macro.
>I thought that LINEST only performs a linear regression, so a regression
>that yields y=mx+n. Obviously it also works for polynomials.
>But does it also work for LN, e-power and x-power equations?
>

Yes. For example, coeffiecients for a logarithmic trendline come from:

=LINEST(B1:B20,LN(A1:A20),,TRUE)

"Power" trendline:

=LINEST(LN(B1:B20),LN(A1:A20),,TRUE)

"Exponential" trendline:

=LINEST(LN(B1:B20),A1:A20,,TRUE)

(You will likely need to use the EXP() function on the constant
returned by LINEST in the case of the "power" and "exponential"
trendlines.)

[Jerry W. Lewis]

In Excel 97 you can call LINEST in VBA
temp = Application.LinEst(...)
And manipulate the elements of the resulting array to get your desired
result.

Jerry W. Lewis
Stat...@erols.com

[M.J.M. Maes]

Thanks for your answers.
Great!

Marcel.