Theoretical question: graph & trendlines in Excel

[ktu]

Hi!

I'm not even sure this is the right group, but I'll try anyway:

Currently I'm programmering a small application in C# to calculate a
trendline similar to the one being used in Excel graphs (basically I
have a set of measuring points, and I want a polynomial that fits best
near those points). I'm not using Excel or any Excel VBA functions
directly, but I'm trying to implement their behaviour.

So far I've been able to find out that Excel uses the "least squares
method" fit if you select "Polynomial" as regression type when adding
a trendline. Works like a charm in my program (the german wikipedia
had a nice article about that "least squares" method, as well as the
"numerical recipes" book).

However, if I set an intercept in the "Options" tab (which is where my
function would cross the y-axis), I don't know how to calculate the
function. Main problem in my case is, least squares is based upon
having n+1 equations with n+1 unknowns for a polynomial y=a0*x^0 +
a1*x^1 + a2*x^2 + ... + an*x^n (n=0,1,...,n), which I can solve e.g.
by a Gauss algorithm. However, if I set the intercept to e.g. 0, which
means that a0=0, then I'd be stuck with n+1 equations but only n
unknowns. Can I still solve this with the least squares method? Any
other suggestions? (I do need a polynomial. Splines/Bezier-curves etc.
are nice but not an option for me)

So, bottom line: least squares works fine for me until I want to use
an intercept. If I use an intercept, which approach would I have to
take to get a polynomial for my measuring points?

Any help (or direction to the appropiate group) would be appreciated.

Cheers,
-Kevin

 

[Jerry W. Lewis]

When the intercept is specified, then you have n equations in n unknowns
y-a0 = a1*x^1 + a2*x^2 + ... + an*x^n

There are many ways to program least squares; most are poor numerically with
challenging data. Excel's chart trendline is actually quite good. You might
try the example from
http://groups.google.com/group/microsoft.public.excel/msg/969a2bb33e6cdbb8
If your routine does not agree with the chart trendline to at least 8
figures, then you should consider using a better algorithm. One source is
http://lib.stat.cmu.edu/apstat/274 (Fortran)
http://lib.stat.cmu.edu/apstat/274-90 (Fortran 90)

Jerry