G
Guest
Looks like LINEST is unable to find the parameters for
y = a2*cos x + a1*sin x + a0
Does anybody know where is the problem?
y = a2*cos x + a1*sin x + a0
Does anybody know where is the problem?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
This equation is linear in cos(x) and sin(x), and therefore should be well
within the capabilities of LINEST. Note however that you will have to pass
LINEST a reference to a 2-column range where one column contains cos(x) and
the other column contains sin(x). Also remember that Excel's trig functions
work in radians, not degrees.
If this does not resolve your problem, then you will need to clarify you
statement. A simple example (text in the body of your post, no attachments
please) goes a long way toward assuring that everybody is on the same page.
Also, LINEST has been evolving over the last few Excel versions, so specify
what version you are using.
Jerry
within the capabilities of LINEST. Note however that you will have to pass
LINEST a reference to a 2-column range where one column contains cos(x) and
the other column contains sin(x). Also remember that Excel's trig functions
work in radians, not degrees.
If this does not resolve your problem, then you will need to clarify you
statement. A simple example (text in the body of your post, no attachments
please) goes a long way toward assuring that everybody is on the same page.
Also, LINEST has been evolving over the last few Excel versions, so specify
what version you are using.
Jerry
G
Guest
Thank you very much for the respond.
I use Office 2003. I did many things with LINEST always successfully. Here
are the data. wt=2*pi*60. I've processed these data manually with no
problems. The equation was -12.62553916*cos wt + 9.034292078*sin wt +
0.992099366
Time cos wt sin wt Cur
0 1 0 -11.396
0.001 0.929776486 0.368124553 -6.2331
0.002 0.728968627 0.684547106 -0.65012
0.003 0.425779292 0.904827052 5.0655
0.004 0.06279052 0.998026728 9.5633
0.005 -0.309016994 0.951056516 13.631
0.006 -0.63742399 0.770513243 16.741
0.007 -0.87630668 0.481753674 17.025
0.008 -0.992114701 0.125333234 14.965
0.009 -0.968583161 -0.248689887 11.688
0.01 -0.809016994 -0.587785252 6.1444
0.011 -0.535826795 -0.844327926 0.88903
0.012 -0.187381315 -0.982287251 -5.9784
0.013 0.187381315 -0.982287251 -9.4566
0.014 0.535826795 -0.844327926 -14.158
0.015 0.809016994 -0.587785252 -13.606
0.016 0.968583161 -0.248689887 -13.13
0.017 0.992114701 0.125333234 -12.214
0.018 0.87630668 0.481753674 -7.1845
0.019 0.63742399 0.770513243 -0.8989
0.02 0.309016994 0.951056516 5.7602
0.021 -0.06279052 0.998026728 10.619
0.022 -0.425779292 0.904827052 15.318
0.023 -0.728968627 0.684547106 16.777
0.024 -0.929776486 0.368124553 16.111
0.025 -1 3.67545E-16 13.771
0.026 -0.929776486 -0.368124553 9.2103
0.027 -0.728968627 -0.684547106 2.375
0.028 -0.425779292 -0.904827052 -3.5757
0.029 -0.06279052 -0.998026728 -7.1228
0.03 0.309016994 -0.951056516 -10.259
0.031 0.63742399 -0.770513243 -13.633
0.032 0.87630668 -0.481753674 -14.558
0.033 0.992114701 -0.125333234 -11.994
0.034 0.968583161 0.248689887 -10.693
0.035 0.809016994 0.587785252 -4.245
0.036 0.535826795 0.844327926 2.5131
0.037 0.187381315 0.982287251 8.319
0.038 -0.187381315 0.982287251 11.284
0.039 -0.535826795 0.844327926 16.991
0.04 -0.809016994 0.587785252 18.281
0.041 -0.968583161 0.248689887 14.912
0.042 -0.992114701 -0.125333234 12.137
0.043 -0.87630668 -0.481753674 8.0685
0.044 -0.63742399 -0.770513243 2.6578
0.045 -0.309016994 -0.951056516 -3.4842
0.046 0.06279052 -0.998026728 -7.0969
0.047 0.425779292 -0.904827052 -10.965
0.048 0.728968627 -0.684547106 -12.618
0.049 0.929776486 -0.368124553 -13.918
0.05 1 -7.35089E-16 -11.19
I use Office 2003. I did many things with LINEST always successfully. Here
are the data. wt=2*pi*60. I've processed these data manually with no
problems. The equation was -12.62553916*cos wt + 9.034292078*sin wt +
0.992099366
Time cos wt sin wt Cur
0 1 0 -11.396
0.001 0.929776486 0.368124553 -6.2331
0.002 0.728968627 0.684547106 -0.65012
0.003 0.425779292 0.904827052 5.0655
0.004 0.06279052 0.998026728 9.5633
0.005 -0.309016994 0.951056516 13.631
0.006 -0.63742399 0.770513243 16.741
0.007 -0.87630668 0.481753674 17.025
0.008 -0.992114701 0.125333234 14.965
0.009 -0.968583161 -0.248689887 11.688
0.01 -0.809016994 -0.587785252 6.1444
0.011 -0.535826795 -0.844327926 0.88903
0.012 -0.187381315 -0.982287251 -5.9784
0.013 0.187381315 -0.982287251 -9.4566
0.014 0.535826795 -0.844327926 -14.158
0.015 0.809016994 -0.587785252 -13.606
0.016 0.968583161 -0.248689887 -13.13
0.017 0.992114701 0.125333234 -12.214
0.018 0.87630668 0.481753674 -7.1845
0.019 0.63742399 0.770513243 -0.8989
0.02 0.309016994 0.951056516 5.7602
0.021 -0.06279052 0.998026728 10.619
0.022 -0.425779292 0.904827052 15.318
0.023 -0.728968627 0.684547106 16.777
0.024 -0.929776486 0.368124553 16.111
0.025 -1 3.67545E-16 13.771
0.026 -0.929776486 -0.368124553 9.2103
0.027 -0.728968627 -0.684547106 2.375
0.028 -0.425779292 -0.904827052 -3.5757
0.029 -0.06279052 -0.998026728 -7.1228
0.03 0.309016994 -0.951056516 -10.259
0.031 0.63742399 -0.770513243 -13.633
0.032 0.87630668 -0.481753674 -14.558
0.033 0.992114701 -0.125333234 -11.994
0.034 0.968583161 0.248689887 -10.693
0.035 0.809016994 0.587785252 -4.245
0.036 0.535826795 0.844327926 2.5131
0.037 0.187381315 0.982287251 8.319
0.038 -0.187381315 0.982287251 11.284
0.039 -0.535826795 0.844327926 16.991
0.04 -0.809016994 0.587785252 18.281
0.041 -0.968583161 0.248689887 14.912
0.042 -0.992114701 -0.125333234 12.137
0.043 -0.87630668 -0.481753674 8.0685
0.044 -0.63742399 -0.770513243 2.6578
0.045 -0.309016994 -0.951056516 -3.4842
0.046 0.06279052 -0.998026728 -7.0969
0.047 0.425779292 -0.904827052 -10.965
0.048 0.728968627 -0.684547106 -12.618
0.049 0.929776486 -0.368124553 -13.918
0.05 1 -7.35089E-16 -11.19
G
Guest
LINEST 2003 introduced a new algorithm that is generally better numerically
than in previous version. However MS appears to have incorporated a matrix
version of the il-advised "optimization" of
http://support.microsoft.com/kb/78113
with the result that LINEST 2003 coefficients that are exactly zero are not
to be trusted. An earlier, more complicated, example is given i
http://groups.google.com/group/micr..._frm/thread/aaa78a91ec42fd4b/71aee5816a59f905
Oddly everything else in the output (other than zero coefficients and the
intercept) appears to be correct (R^2, etc). This problem appears to occur
with columns that are essentially orthogonal, which also have the same vector
norm as each other. Fortunately this is exactly the situation where directly
forming and solving the normal equations performs best numerically.
Assuming that your angles run from 0 to 1080 degrees in 21.6 degree
increments, then the X'X matrix should be
51 1 0
1 26 0
0 0 25
and the X'y vector is
50.55891
-328.0164586
225.8573019
which leads to the solution -12.66369683*cos wt + 9.034292078*sin wt +
1.239658957. Your cos and intercept terms differ too much from these values
to be attributed to accumulated round-off; how did you calculate them?
LINEST in older versions of Excel would handle this calculation with no
problem, but could give incorrect results without warning on more complicated
calculations. LINEST in Excel 2007 retains the better numerical algorithm
while correcting this bug. Unfortunately, they "improved" the formerly
excellent chart trendline fitting routine to the point that it is no longer
trustworthy
http://groups.google.com/group/micr..._frm/thread/4328cd532baaa078/f625e5a0b1867721
and other threads that it refers too
Jerry
than in previous version. However MS appears to have incorporated a matrix
version of the il-advised "optimization" of
http://support.microsoft.com/kb/78113
with the result that LINEST 2003 coefficients that are exactly zero are not
to be trusted. An earlier, more complicated, example is given i
http://groups.google.com/group/micr..._frm/thread/aaa78a91ec42fd4b/71aee5816a59f905
Oddly everything else in the output (other than zero coefficients and the
intercept) appears to be correct (R^2, etc). This problem appears to occur
with columns that are essentially orthogonal, which also have the same vector
norm as each other. Fortunately this is exactly the situation where directly
forming and solving the normal equations performs best numerically.
Assuming that your angles run from 0 to 1080 degrees in 21.6 degree
increments, then the X'X matrix should be
51 1 0
1 26 0
0 0 25
and the X'y vector is
50.55891
-328.0164586
225.8573019
which leads to the solution -12.66369683*cos wt + 9.034292078*sin wt +
1.239658957. Your cos and intercept terms differ too much from these values
to be attributed to accumulated round-off; how did you calculate them?
LINEST in older versions of Excel would handle this calculation with no
problem, but could give incorrect results without warning on more complicated
calculations. LINEST in Excel 2007 retains the better numerical algorithm
while correcting this bug. Unfortunately, they "improved" the formerly
excellent chart trendline fitting routine to the point that it is no longer
trustworthy
http://groups.google.com/group/micr..._frm/thread/4328cd532baaa078/f625e5a0b1867721
and other threads that it refers too
Jerry