Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Theoretical question: graph & trendlines in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Theoretical question: graph & trendlines in Excel
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/micro...9a2bb33e6cdbb8 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 "ktu" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 graph question | Excel Discussion (Misc queries) | |||
Question about Excel Graph | Charts and Charting in Excel | |||
Workesheet Calculate - strange behaviour and theoretical question... | Excel Programming | |||
Trendlines in Excel | Charts and Charting in Excel | |||
Logarithmic Trendlines in Excel | Charts and Charting in Excel |