Excel Minverse function has truncation errors, and I would like to use
Gaussian Reduction to solve my eqn ( A.X = Y ) without using matrix
inversion.
I understand the theory and the maths - has anyone done this in MSExcel, or
has any ideas how I might do this?

[Basically, it involves Mmult with a triangular matrix to reduce terms below
the diagonal to zeros, until you then solve 'by inspection' from the bottom
up. Triangle matrix is similar to the identity matrix, but with one key
value replacing one of the zeros]

pseudo-code is presented in:

http://en.wikipedia.org/wiki/Gaussian_reduction

You would need to translate it to VBA.
--
Gary''s Student - gsnu200829


"David C" wrote:

Excel Minverse function has truncation errors, and I would like to use
Gaussian Reduction to solve my eqn ( A.X = Y ) without using matrix
inversion.
I understand the theory and the maths - has anyone done this in MSExcel, or
has any ideas how I might do this?

[Basically, it involves Mmult with a triangular matrix to reduce terms below
the diagonal to zeros, until you then solve 'by inspection' from the bottom
up. Triangle matrix is similar to the identity matrix, but with one key
value replacing one of the zeros]

Is there any guaranty that the VBA code he write will give results any
better than MINVERSE?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gary''s Student" wrote in message
...
pseudo-code is presented in:

http://en.wikipedia.org/wiki/Gaussian_reduction

You would need to translate it to VBA.
--
Gary''s Student - gsnu200829


"David C" wrote:

Excel Minverse function has truncation errors, and I would like to use
Gaussian Reduction to solve my eqn ( A.X = Y ) without using matrix
inversion.
I understand the theory and the maths - has anyone done this in MSExcel,
or
has any ideas how I might do this?

[Basically, it involves Mmult with a triangular matrix to reduce terms
below
the diagonal to zeros, until you then solve 'by inspection' from the
bottom
up. Triangle matrix is similar to the identity matrix, but with one key
value replacing one of the zeros]

No guarantee at all!

Clearly David is having problems with some of his matrices. The code in
wikipedia is small enough that it could be re-coded in a couple of hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.
--
Gary''s Student - gsnu200829


"Bernard Liengme" wrote:

Is there any guaranty that the VBA code he write will give results any
better than MINVERSE?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gary''s Student" wrote in message
...
pseudo-code is presented in:

http://en.wikipedia.org/wiki/Gaussian_reduction

You would need to translate it to VBA.
--
Gary''s Student - gsnu200829


"David C" wrote:

Excel Minverse function has truncation errors, and I would like to use
Gaussian Reduction to solve my eqn ( A.X = Y ) without using matrix
inversion.
I understand the theory and the maths - has anyone done this in MSExcel,
or
has any ideas how I might do this?

[Basically, it involves Mmult with a triangular matrix to reduce terms
below
the diagonal to zeros, until you then solve 'by inspection' from the
bottom
up. Triangle matrix is similar to the identity matrix, but with one key
value replacing one of the zeros]

Could be just a high "Condition Number" that's associated with his matrix.

http://en.wikipedia.org/wiki/Matrix_condition_number

Dana DeLouis


Gary''s Student wrote:
No guarantee at all!

Clearly David is having problems with some of his matrices. The code in
wikipedia is small enough that it could be re-coded in a couple of hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.

Thank you all - what I found was that Minverse was giving silly results, or
even ERROR messages, which could be changed by small changes in some of the
'A' matrix numbers; ie the fault may well lie in the source data not the
process. I wanted an alternative (and more compact) process to compare
results, to determine where the fault may lie.
Using Minverse to solve the Cosine matrix for a cyclic model, sometimes the
resulting coefficients were divergent too, adding to the instability. The
results, when reconstructed and compared with the data, did not even pass
through the data points.
f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
F(a) = [ a0, a1, ... an, ... ]
Cos matrix . F(a) matrix = f(A) matrix

Still working the problem - grateful for comments.

"Dana DeLouis" wrote:

Could be just a high "Condition Number" that's associated with his matrix.

http://en.wikipedia.org/wiki/Matrix_condition_number

Dana DeLouis


Gary''s Student wrote:
No guarantee at all!

Clearly David is having problems with some of his matrices. The code in
wikipedia is small enough that it could be re-coded in a couple of hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.

Hi. Using LU Decomposition can sometimes be a little more stable.
If also offers a few other benefits.

http://en.wikipedia.org/wiki/LU_decomposition

Feel free to send me your data
= = =
Dana DeLouis



David C wrote:
Thank you all - what I found was that Minverse was giving silly results, or
even ERROR messages, which could be changed by small changes in some of the
'A' matrix numbers; ie the fault may well lie in the source data not the
process. I wanted an alternative (and more compact) process to compare
results, to determine where the fault may lie.
Using Minverse to solve the Cosine matrix for a cyclic model, sometimes the
resulting coefficients were divergent too, adding to the instability. The
results, when reconstructed and compared with the data, did not even pass
through the data points.
f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
F(a) = [ a0, a1, ... an, ... ]
Cos matrix . F(a) matrix = f(A) matrix

Still working the problem - grateful for comments.

"Dana DeLouis" wrote:

Could be just a high "Condition Number" that's associated with his matrix.

http://en.wikipedia.org/wiki/Matrix_condition_number

Dana DeLouis


Gary''s Student wrote:
No guarantee at all!

Clearly David is having problems with some of his matrices. The code in
wikipedia is small enough that it could be re-coded in a couple of hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.

Thanks -
A = {10,30,50,70,90,110,130,150,170,190,210,230,250,27 0,290,310,330,350} ;
angles in degrees.
f(A) =
{0.87,0.8,1,1,0.98,0.98,0.98,0.75,0.65,0.69,0.97,0 .98,1,0.98,0.95,0.73,0.77,0.87} ; observations.
model f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
ie Cos matrix . F(a) matrix = f(A) matrix
F(a) = {a0, a1, ... an, ...}
find F(a)


"Dana DeLouis" wrote:


Hi. Using LU Decomposition can sometimes be a little more stable.
If also offers a few other benefits.

http://en.wikipedia.org/wiki/LU_decomposition

Feel free to send me your data
= = =
Dana DeLouis



David C wrote:
Thank you all - what I found was that Minverse was giving silly results, or
even ERROR messages, which could be changed by small changes in some of the
'A' matrix numbers; ie the fault may well lie in the source data not the
process. I wanted an alternative (and more compact) process to compare
results, to determine where the fault may lie.
Using Minverse to solve the Cosine matrix for a cyclic model, sometimes the
resulting coefficients were divergent too, adding to the instability. The
results, when reconstructed and compared with the data, did not even pass
through the data points.
f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
F(a) = [ a0, a1, ... an, ... ]
Cos matrix . F(a) matrix = f(A) matrix

Still working the problem - grateful for comments.

"Dana DeLouis" wrote:

Could be just a high "Condition Number" that's associated with his matrix.

http://en.wikipedia.org/wiki/Matrix_condition_number

Dana DeLouis


Gary''s Student wrote:
No guarantee at all!

Clearly David is having problems with some of his matrices. The code in
wikipedia is small enough that it could be re-coded in a couple of hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.

Hi Dave,
I used Solver and got these results
coeff0 0.872399
coeff1 0.01781
coeff2 0.006306
coeff3 -0.01764
coeff4 0.024949
coeff5 0.124181
coeff6 0.138704
coeff7 0.067487
coeff8 -0.03891
coeff9 -0.01647
coeff10 0.078441


The sum of deviations squared was 0.091386
Send me email (remove TRUENORTH.) and I will send you a file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David C" wrote in message
...
Thanks -
A = {10,30,50,70,90,110,130,150,170,190,210,230,250,27 0,290,310,330,350} ;
angles in degrees.
f(A) =
{0.87,0.8,1,1,0.98,0.98,0.98,0.75,0.65,0.69,0.97,0 .98,1,0.98,0.95,0.73,0.77,0.87}
; observations.
model f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
ie Cos matrix . F(a) matrix = f(A) matrix
F(a) = {a0, a1, ... an, ...}
find F(a)


"Dana DeLouis" wrote:


Hi. Using LU Decomposition can sometimes be a little more stable.
If also offers a few other benefits.

http://en.wikipedia.org/wiki/LU_decomposition

Feel free to send me your data
= = =
Dana DeLouis



David C wrote:
Thank you all - what I found was that Minverse was giving silly
results, or
even ERROR messages, which could be changed by small changes in some of
the
'A' matrix numbers; ie the fault may well lie in the source data not
the
process. I wanted an alternative (and more compact) process to compare
results, to determine where the fault may lie.
Using Minverse to solve the Cosine matrix for a cyclic model, sometimes
the
resulting coefficients were divergent too, adding to the instability.
The
results, when reconstructed and compared with the data, did not even
pass
through the data points.
f(A) = a0 + a1.cos A + ... + an.cos n.A + ...
F(a) = [ a0, a1, ... an, ... ]
Cos matrix . F(a) matrix = f(A) matrix

Still working the problem - grateful for comments.

"Dana DeLouis" wrote:

Could be just a high "Condition Number" that's associated with his
matrix.

http://en.wikipedia.org/wiki/Matrix_condition_number

Dana DeLouis


Gary''s Student wrote:
No guarantee at all!

Clearly David is having problems with some of his matrices. The code
in
wikipedia is small enough that it could be re-coded in a couple of
hours.

David could compare the results from the Gaussian method to whatever
Microsoft uses.

Hi. May I ask how you set this up? I'm a little lost.
I was expecting a n*n array. I guess I don't understand the setup thou.
I noticed that the Abs values of the Fourier coefficients are close to
what you have. Is the OP trying to do a 1-dimensional Cos Trig fit?
These are not phased together by the angles given, just the abs values.

0.88611111,
0.018898028,
0.13342994,
0.064291005,
0.015228924,
0.064608047,
0.018986675,
0.0075864187,
0.0035608633,
0.021666667

= = =
Dana DeLouis

Bernard Liengme wrote:
Hi Dave,
I used Solver and got these results
coeff0 0.872399
coeff1 0.01781
coeff2 0.006306
coeff3 -0.01764
coeff4 0.024949
coeff5 0.124181
coeff6 0.138704
coeff7 0.067487
coeff8 -0.03891
coeff9 -0.01647
coeff10 0.078441


The sum of deviations squared was 0.091386
Send me email (remove TRUENORTH.) and I will send you a file
best wishes

Hi Dana,
I took this to be a simple curve fitting exercise

In A2:A19 the values 10, 30, 50, 70 etc
In B2:B19 the values 0.87, 0.8, 1, 1, etc
We return to column C soon
In E1:E10 the next: coeff0, coeff1,....coeff10
In F1:F10 the value 1 in each cell
Select E1:F10 and create names for the F values
In C1 the formula
=coeff0+coeff1*COS(A2)+coeff2*COS(A2*2)+coeff3*COS (A2*3)+coeff4*COS(A2*4)+coeff5*COS(A2*5)+coeff6*CO S(A2*6)+coeff7*COS(A2*7)+coeff8*COS(A2*8)+coeff9*C OS(A2*9)+coeff10*COS(A2*10)
Copy down the column
In H1 the formula =SUMXMY2(B2:B19,C2:C19) to compute the sum of squares of
deviations
Solver: minimize H1 by changing the coeff values

The ssd is too high - a plot of shows only a fair degree of agreement
between data in B with that in C. Might need to add more coeff

I feel a little awkward telling you how to do something - your math skills
far exceed those a chemist

best wishes

--
Bernard
"Dana DeLouis" wrote in message
...
Hi. May I ask how you set this up? I'm a little lost.
I was expecting a n*n array. I guess I don't understand the setup thou.
I noticed that the Abs values of the Fourier coefficients are close to
what you have. Is the OP trying to do a 1-dimensional Cos Trig fit?
These are not phased together by the angles given, just the abs values.

0.88611111,
0.018898028,
0.13342994,
0.064291005,
0.015228924,
0.064608047,
0.018986675,
0.0075864187,
0.0035608633,
0.021666667

= = =
Dana DeLouis

Bernard Liengme wrote:
Hi Dave,
I used Solver and got these results
coeff0 0.872399
coeff1 0.01781
coeff2 0.006306
coeff3 -0.01764
coeff4 0.024949
coeff5 0.124181
coeff6 0.138704
coeff7 0.067487
coeff8 -0.03891
coeff9 -0.01647
coeff10 0.078441


The sum of deviations squared was 0.091386
Send me email (remove TRUENORTH.) and I will send you a file
best wishes

Hi Bernard. Thank you very much for the info. :)
I might be wrong, but here are some thought on the issue.
My experience is that Solver would not work very well here for the
following reasons. With multiple Cos() functions, any plot would show
numerous peaks and valleys. Solver uses Finite differences for a
derivative, and would most likely lock on to a "Local" minimum. Solver
really has no way of telling if it's a Global minimum (or maximum).
When multiple cells are squared, it has no idea what's going on.
For example, both -3 and +3 return 9 (squared). It can't really use
this info to determine a next step. Plus, all the squared terms are
fighting each other in opposite directions.

I might be wrong, but I re-read the problem and "think" I understand
what the op is trying to do.

Using Minverse to solve the Cosine matrix

Just guessing of course...
I believe this is his Cosine Matrix...(Note: Cos(0) - 1)
I'll just work with 4 points for now...

m = {1, Cos[10], Cos[20], Cos[30]},
{1, Cos[30], Cos[60], Cos[90]},
{1, Cos[50], Cos[100], Cos[150]},
{1, Cos[70], Cos[140], Cos[210]}

Coefficients:
coef = {a0, a1, a2, a3};

Right-hand side data:
rhs = {0.87, 0.8, 1, 1};

Then I think the op is trying to solve the following equations:

m.coef == rhs

{a0 + a1 Cos[10] + a2 Cos[20] + a3 Cos[30] = 0.87,
a0 + a1 Cos[30] + a2 Cos[60] + a3 Cos[90] = 0.8,
a0 + a1 Cos[50] + a2 Cos[100] + a3 Cos[150] = 1,
a0 + a1 Cos[70] + a2 Cos[140] + a3 Cos[210] = 1}

If we expand the Matrix out to 18*18, then the solution should not be a
problem.
My guess is that the op forgot to mention that the numbers are in Degrees!
If those are in degrees, then the Matrix is Singular (ie the Determinant
is zero).
This might account for the problems mentioned.

If they are not in degrees, then Solving the 18 equations in 18 unknowns
yields the following coefficients...

0.80840394,
-0.13310907,
-0.050053729,
-0.028541588,
0.031492505,
0.046523792,
0.0084226285,
0.0804206,
-0.04478755,
-0.054984442,
-0.031472364,
-0.10809329,
-0.087294611,
-0.039289436,
-0.01470285,
-0.023693654,
-0.0030566841,
-0.1441084

Again, I'm just guessing here.
= = =
Dana DeLouis



Bernard Liengme wrote:
Hi Dana,
I took this to be a simple curve fitting exercise

In A2:A19 the values 10, 30, 50, 70 etc
In B2:B19 the values 0.87, 0.8, 1, 1, etc
We return to column C soon
In E1:E10 the next: coeff0, coeff1,....coeff10
In F1:F10 the value 1 in each cell
Select E1:F10 and create names for the F values
In C1 the formula
=coeff0+coeff1*COS(A2)+coeff2*COS(A2*2)+coeff3*COS (A2*3)+coeff4*COS(A2*4)+coeff5*COS(A2*5)+coeff6*CO S(A2*6)+coeff7*COS(A2*7)+coeff8*COS(A2*8)+coeff9*C OS(A2*9)+coeff10*COS(A2*10)
Copy down the column
In H1 the formula =SUMXMY2(B2:B19,C2:C19) to compute the sum of squares of
deviations
Solver: minimize H1 by changing the coeff values

The ssd is too high - a plot of shows only a fair degree of agreement
between data in B with that in C. Might need to add more coeff

I feel a little awkward telling you how to do something - your math skills
far exceed those a chemist

best wishes

Oops. Me bad!! I can't read!
The op did say the angels were in degrees.

A = {10,30,50,70,90,... ;
angles in degrees.


I believe the Cosine Matrix is Singular.

= = =
Dana DeLouis

Hi. I'll just throw something out.
Since I believe the Cosine Matrix is Singular, the op might want to
research code called something like "Pseudo Inverse"
The following would be his desired coefficients.

PInv = PseudoInverse(m).rhs

0.88611111
0.0058843076
-0.059559065
0.031754265
-0.006145086
0.024229959
0.0055555556
0.0033049841
-0.00091397874
0
0.00091397874
-0.0033049841
-0.0055555556
-0.024229959
0.006145086
-0.031754265
0.059559065
-0.0058843076

These won't cause an exact solution, but it's as close as you can get.
Here are the errors in the solution...

m.PInv - rhs

{0, -0.015, -0.135, -0.025, 0, 0.01, 0, 0.11, 0.02, -0.02, -0.11, 0,
-0.01, 0, 0.025, 0.135, 0.015, 0}

I don't believe you can find a solution "closer" than the above given
your Cosine Matrix is Singular.
I could be wrong though...

= = =
Dana DeLouis



Dana DeLouis wrote:
Oops. Me bad!! I can't read!
The op did say the angels were in degrees.

A = {10,30,50,70,90,... ;
angles in degrees.


I believe the Cosine Matrix is Singular.

= = =
Dana DeLouis

Dana:
Me bad also - forgot the degree thing
I replaced the 10, 20, etc by RADIANS(1), RADIANS(20).....
Did the Solver thing as before and got
coeff0 0.886111
coeff1 0.011769
coeff2 -0.11912
coeff3 0.063509
coeff4 -0.01229
coeff5 0.04846
coeff6 0.011111
coeff7 0.00661
coeff8 0.018849
coeff9 -0.01647
coeff10 0.020677


The 'raw' and 'fitted' data now reasonable but not great when plotted.
Please note we are not finding a min the data, we are minimizing Sum of
Deviations² (SSD)
So Solver 'plays' with coeff values until this SSD is minimized
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dana DeLouis" wrote in message
...
Hi Bernard. Thank you very much for the info. :)
I might be wrong, but here are some thought on the issue.
My experience is that Solver would not work very well here for the
following reasons. With multiple Cos() functions, any plot would show
numerous peaks and valleys. Solver uses Finite differences for a
derivative, and would most likely lock on to a "Local" minimum. Solver
really has no way of telling if it's a Global minimum (or maximum).
When multiple cells are squared, it has no idea what's going on.
For example, both -3 and +3 return 9 (squared). It can't really use this
info to determine a next step. Plus, all the squared terms are fighting
each other in opposite directions.

I might be wrong, but I re-read the problem and "think" I understand what
the op is trying to do.

Using Minverse to solve the Cosine matrix

Just guessing of course...
I believe this is his Cosine Matrix...(Note: Cos(0) - 1)
I'll just work with 4 points for now...

m = {1, Cos[10], Cos[20], Cos[30]},
{1, Cos[30], Cos[60], Cos[90]},
{1, Cos[50], Cos[100], Cos[150]},
{1, Cos[70], Cos[140], Cos[210]}

Coefficients:
coef = {a0, a1, a2, a3};

Right-hand side data:
rhs = {0.87, 0.8, 1, 1};

Then I think the op is trying to solve the following equations:

m.coef == rhs

{a0 + a1 Cos[10] + a2 Cos[20] + a3 Cos[30] = 0.87,
a0 + a1 Cos[30] + a2 Cos[60] + a3 Cos[90] = 0.8,
a0 + a1 Cos[50] + a2 Cos[100] + a3 Cos[150] = 1,
a0 + a1 Cos[70] + a2 Cos[140] + a3 Cos[210] = 1}

If we expand the Matrix out to 18*18, then the solution should not be a
problem.
My guess is that the op forgot to mention that the numbers are in Degrees!
If those are in degrees, then the Matrix is Singular (ie the Determinant
is zero).
This might account for the problems mentioned.

If they are not in degrees, then Solving the 18 equations in 18 unknowns
yields the following coefficients...

0.80840394,
-0.13310907,
-0.050053729,
-0.028541588,
0.031492505,
0.046523792,
0.0084226285,
0.0804206,
-0.04478755,
-0.054984442,
-0.031472364,
-0.10809329,
-0.087294611,
-0.039289436,
-0.01470285,
-0.023693654,
-0.0030566841,
-0.1441084

Again, I'm just guessing here.
= = =
Dana DeLouis



Bernard Liengme wrote:
Hi Dana,
I took this to be a simple curve fitting exercise

In A2:A19 the values 10, 30, 50, 70 etc
In B2:B19 the values 0.87, 0.8, 1, 1, etc
We return to column C soon
In E1:E10 the next: coeff0, coeff1,....coeff10
In F1:F10 the value 1 in each cell
Select E1:F10 and create names for the F values
In C1 the formula
=coeff0+coeff1*COS(A2)+coeff2*COS(A2*2)+coeff3*COS (A2*3)+coeff4*COS(A2*4)+coeff5*COS(A2*5)+coeff6*CO S(A2*6)+coeff7*COS(A2*7)+coeff8*COS(A2*8)+coeff9*C OS(A2*9)+coeff10*COS(A2*10)
Copy down the column
In H1 the formula =SUMXMY2(B2:B19,C2:C19) to compute the sum of squares
of deviations
Solver: minimize H1 by changing the coeff values

The ssd is too high - a plot of shows only a fair degree of agreement
between data in B with that in C. Might need to add more coeff

I feel a little awkward telling you how to do something - your math
skills far exceed those a chemist

best wishes

Thanks all, especially Bernard and Dana

I guess I was so wrapped up in the task, that I was unable to explain the
problem clearly enough, but you figured it out anyway! The 'singular' thingy
was the techy word for my "Minverse was giving silly results, or even ERROR
messages, which could be changed by small changes in some of the 'A' matrix
numbers; ie the fault may well lie in the source data not the process .."
which I avoided by adding 0.1 rad to every angle reading 'A' (ie the graph
plot is moved ever so slightly to the right / clockwise).

The coefficients, F(a), then are
{0.7,0.3,-0.4,0.3,-0.3,0.2,-0.2,0.2,-0.2,0.1,-0.1,0,0, ....}. Sadly, the
reconstituted model f(A) does not go through, or in some cases anywhere near,
the observed values. I would have accepted close misses, but not this big.
Therefore I sought an alternative way to solve A.X = Y

"Bernard Liengme" wrote:

Dana:
Me bad also - forgot the degree thing
I replaced the 10, 20, etc by RADIANS(1), RADIANS(20).....
Did the Solver thing as before and got
coeff0 0.886111
coeff1 0.011769
coeff2 -0.11912
coeff3 0.063509
coeff4 -0.01229
coeff5 0.04846
coeff6 0.011111
coeff7 0.00661
coeff8 0.018849
coeff9 -0.01647
coeff10 0.020677


The 'raw' and 'fitted' data now reasonable but not great when plotted.
Please note we are not finding a min the data, we are minimizing Sum of
Deviations² (SSD)
So Solver 'plays' with coeff values until this SSD is minimized
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dana DeLouis" wrote in message
...
Hi Bernard. Thank you very much for the info. :)
I might be wrong, but here are some thought on the issue.
My experience is that Solver would not work very well here for the
following reasons. With multiple Cos() functions, any plot would show
numerous peaks and valleys. Solver uses Finite differences for a
derivative, and would most likely lock on to a "Local" minimum. Solver
really has no way of telling if it's a Global minimum (or maximum).
When multiple cells are squared, it has no idea what's going on.
For example, both -3 and +3 return 9 (squared). It can't really use this
info to determine a next step. Plus, all the squared terms are fighting
each other in opposite directions.

I might be wrong, but I re-read the problem and "think" I understand what
the op is trying to do.

Using Minverse to solve the Cosine matrix

Just guessing of course...
I believe this is his Cosine Matrix...(Note: Cos(0) - 1)
I'll just work with 4 points for now...

m = {1, Cos[10], Cos[20], Cos[30]},
{1, Cos[30], Cos[60], Cos[90]},
{1, Cos[50], Cos[100], Cos[150]},
{1, Cos[70], Cos[140], Cos[210]}

Coefficients:
coef = {a0, a1, a2, a3};

Right-hand side data:
rhs = {0.87, 0.8, 1, 1};

Then I think the op is trying to solve the following equations:

m.coef == rhs

{a0 + a1 Cos[10] + a2 Cos[20] + a3 Cos[30] = 0.87,
a0 + a1 Cos[30] + a2 Cos[60] + a3 Cos[90] = 0.8,
a0 + a1 Cos[50] + a2 Cos[100] + a3 Cos[150] = 1,
a0 + a1 Cos[70] + a2 Cos[140] + a3 Cos[210] = 1}

If we expand the Matrix out to 18*18, then the solution should not be a
problem.
My guess is that the op forgot to mention that the numbers are in Degrees!
If those are in degrees, then the Matrix is Singular (ie the Determinant
is zero).
This might account for the problems mentioned.

If they are not in degrees, then Solving the 18 equations in 18 unknowns
yields the following coefficients...

0.80840394,
-0.13310907,
-0.050053729,
-0.028541588,
0.031492505,
0.046523792,
0.0084226285,
0.0804206,
-0.04478755,
-0.054984442,
-0.031472364,
-0.10809329,
-0.087294611,
-0.039289436,
-0.01470285,
-0.023693654,
-0.0030566841,
-0.1441084

Again, I'm just guessing here.
= = =
Dana DeLouis



Bernard Liengme wrote:
Hi Dana,
I took this to be a simple curve fitting exercise

In A2:A19 the values 10, 30, 50, 70 etc
In B2:B19 the values 0.87, 0.8, 1, 1, etc
We return to column C soon
In E1:E10 the next: coeff0, coeff1,....coeff10
In F1:F10 the value 1 in each cell
Select E1:F10 and create names for the F values
In C1 the formula
=coeff0+coeff1*COS(A2)+coeff2*COS(A2*2)+coeff3*COS (A2*3)+coeff4*COS(A2*4)+coeff5*COS(A2*5)+coeff6*CO S(A2*6)+coeff7*COS(A2*7)+coeff8*COS(A2*8)+coeff9*C OS(A2*9)+coeff10*COS(A2*10)
Copy down the column
In H1 the formula =SUMXMY2(B2:B19,C2:C19) to compute the sum of squares
of deviations
Solver: minimize H1 by changing the coeff values

The ssd is too high - a plot of shows only a fair degree of agreement
between data in B with that in C. Might need to add more coeff

I feel a little awkward telling you how to do something - your math
skills far exceed those a chemist

best wishes

Sadly, the reconstituted model f(A) does not go through, or in some
cases anywhere near, the observed values.
I would have accepted close misses

Hi. Just throwing something out.
It appears that you are trying to work in the frequency domain based on
your Cosine Matrix. It appears to me you want a solution with Cosine's
only. If so, how about the Fourier equation? You can't get an exact
fit without the Sin portion to give you the correct phase shift for each
frequency. Here is the equation. Note that your 18 points are now
numbered 0-17, and not 1-18.

Sub Demo()
Dim Pi As Double
Dim j As Long

For j = 0 To 17
Debug.Print j; CosFit(j)
Next j
End Sub



Function CosFit(x)
Dim Pi As Double
Pi = [Pi()]
CosFit = _
0.886111111111111 + _
0.018898028106003 * Cos(0.724019780381624 - (Pi * x) / 9) + _
0.133429937703512 * Cos(2.32511875642987 - (2 * Pi * x) / 9) + _
6.42910050732863E-02 * Cos(0.679775646576795 + (Pi * x) / 3) + _
0.01522892397659 * Cos(1.81176681414226 - (4 * Pi * x) / 9) + _
6.46080465147374E-02 * Cos(1.59530802116179 + (5 * Pi * x) / 9) + _
1.89866749895945E-02 * Cos(1.99286012557845 + (2 * Pi * x) / 3) + _
7.58641866384528E-03 * Cos(0.708758198743175 + (7 * Pi * x) / 9) + _
3.56086325212297E-03 * Cos(2.77704553008904 - (8 * Pi * x) / 9) + _
2.16666666666666E-02 * Cos(Pi * x)

End Function


Note that your 18 points are divided into 360 (ie 2 Pi/18 - Pi/9)

Here are the results:

0 0.87
1 0.8
2 1
3 1
4 0.98
5 0.98
6 0.98
7 0.75
8 0.65
9 0.69
10 0.97
11 0.98
12 1
13 0.98
14 0.95
15 0.73
16 0.77
17 0.87


Which match what you wanted for the output:

f(A) =
{0.87,0.8,1,1,0.98,0.98,0.98,0.75,0.65,0.69,0.97,0 .98,1,0.98,0.95,0.73,0.77,0.87}



Probably not useful, but I thought I would mention it.

= = =
Dana DeLouis