LINEST for trigonometry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BR - 10 Jul 2007 15:38 GMT

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?

Reply to this Message

Jerry W. Lewis - 10 Jul 2007 20:46 GMT

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

> 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?

Reply to this Message

BR - 16 Jul 2007 19:24 GMT

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

Reply to this Message

Jerry W. Lewis - 17 Jul 2007 16:42 GMT

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/microsoft.public.excel.programming/browse_frm/thr
ead/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/microsoft.public.excel.charting/browse_frm/thread
/4328cd532baaa078/f625e5a0b1867721
and other threads that it refers too

Jerry

> Thank you very much for the respond.
> I use Office 2003. I did many things with LINEST always successfully. Here
[quoted text clipped - 54 lines]
> 0.049 0.929776486 -0.368124553 -13.918
> 0.05 1 -7.35089E-16 -11.19

Reply to this Message