Show Equation via macro

[#1]

Hi All,

I create a macro that add a trend line to a chart. How can I add the trend line equation via macro?

This is the macro:

sub add_lintrendline
set chart = ActiveDocument.GetSheetObject( "CH50" )
chart.AddTrend 1,1,1
set p = chart.GetProperties
p.ChartProperties.TrendLineWidth = 5
chart.SetProperties p
end sub

Thanks

[#2]

The trendline equation is defined by the parameters passed to AddTrend. The first parameter tells you which expression to trend. The second is the type of trend (1 for polynomial, 2 for exponential). Then if you choose polynomial, the third parameter tells you which degree.

So if you want to set a Polynomial of the 2nd Degree on the first expressions, you would use:

AddTrend(0,1,2)

The third parameter is (only when using 1 as the second parameter):
0 Mean
1 Linear
2 Polynomial 2nd Degree
3 Polynomial 3nd Degree
4 Polynomial 4nd Degree

[#3]

Thank you for the explanation.

But I want to show (via macro) the equation on the chart, like this:

[#4]

I don't know that it is possible to get that equation into a macro. I did not see anything in the API that would allow that. It could be available but undocumented.

If you really wanted to make your life difficult, you could probably determine the equation of the trend line yourself. There are probably equations out there to determine a trend line. I would probably tell the users it was impossible before I tried that though.

[#5]

I found this example in API guide, but I think that it is not useful for me:

set chart = ActiveDocument.ActiveSheet.CreateBarChart
chart.AddDimension "ProductType"
chart.AddExpression "count(Customer)"
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ShowAsBar = true
rem add linear regression trend line
expr.ExpressionTypes.Add
expr.ExpressionTypes(0).Type = 1
expr.ExpressionTypes(0).Order = 1
expr.ExpressionTypes(0).DisplayEquation = true
chart.SetProperties p

Any ideas????

[Anonymous]

That should be the one. The DisplayEquation member should allow you to set the trendline to be shown or hidden as it's read-write.

expr.ExpressionTypes(0).DisplayEquation = true

Just make sure that you assign the correct expression to expr.

In this example it's set to the first expression (number 0):

set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual

[#7]

Thanks Johannes.

It's works for average, lineare and polynomial (2nd, 3th and 4th degree) but not for exponential, to do this I use this:

expr.ExpressionTypes.Add
expr.ExpressionTypes(0).Type = 2
expr.ExpressionTypes(0).Order = 1
expr.ExpressionTypes(0).DisplayEquation = true

but the exponential trend doesn't appear. Why?

[mantaq10]

Hi all,

Interesting thread..

i was wondering if i can reuse the linear regression equation elsewhere. That is, i am looking for a function which extracts the equation as a string for me to treat it as an expression? something like the linest_* family of functions?

Actually, i dont get the same regression equation when i generate it as a trendline from the chart properties and when i run the functions on the same two fields.