Trendline Linear Algorithm

[Bin]

What is the algorithm for creating a linear Trendline for
some Excell Data? I am working on a macro to try get the
linear trendline equation for some data point. Is there
anyone can help me? Thank you.

 

[Jerry W. Lewis]

If you mean a straight line (vs. a model that is linear in the unkowns),
Help for the worksheet functions SLOPE and INTERCEPT give formulas. For
more complicated linear models, you can use LINEST, which mathemetically
can be described as a relatively simple matrix equation, including
matrix inversion. Numerically a much more complicated calculation is
preferred. SLOPE, INTERCEPT, FORECAST, LINEST, and TREND can all be
called from VBA, so it is unclear why you would want to program routines
from scratch unless you understand the calculations well enough to get
better numerical properties than the worksheet functions give.

Jerry

 

[Guest]

The trendlines in Excel use regression analysis. For a straight line analysis, go get a college math text and look up the section on "Linear Regression". You are looking for an equation of the form y=a+bx. Regression analysis of "n" data points will give you the coefficient b={SUM(x*y) - 1/n*[SUM(x)*SUM(y)]}/{SUM(y^2)-1/n*SUM(y)^2}. Then you plug in "b" using one of your ordered pairs and solve for "a". An excellent reference is "The VNR Concise Encyclopedia of Mathematics" by W. Gellert, H. Kustner, M. Hellwich and H. Kastner, published by Van Nostrand Reinhold Co. ISBN 0-442-22646-2. page 599. Also, most editions of the "CRC Standard Math Tables and Formulae" have the information.