Possible to solve overdetermined system of equations directly in Excel?

[Special K]

I have the following overdetermined system of equations (3 unknowns, 9 equations):

16*a + 2*b + 0*c = 14
29*a + 7*b + 0*c = 25
38*a + 7*b + 0*c = 33

5*a + 20*b + 1*c = 5
8*a + 35*b + 2*c = 9
0*a + 5*b + 41*c = 4

0*a + 4*b + 30*c = 3
0*a + 5*b + 41*c = 4
0*a + 2*b + 23*c = 2

Is there a way to directly find the least squares solution in Excel given the above data? I've been reading about functions such as LINEST however I can't seem to map what I have above to the terminology used in the function description and suspect it's just an issue of my not fully understanding what I'm working with.

 

[pgc01]

Hi

If I remember correctly the usual way to get the the least squares solution of an overdetermined system of equations is to use the transpose matrix of the coefficients which you can do easily in excel, since you have the excel matrix functions.

In the case of excel, however, since you have Linest(), you can calculate it directly.
If your coefficient values are in A1:C9 and the independent values in D1:D9, try:

=LINEST(D1:D9,A1:C9,FALSE)

I just tried and got a=0.857147808935188, b=0.0497063183306886, c=0.0908866985301637

You can also try with the transpose matrix solution just to confirm.

Remark: in excel you could also do it with the solver. You'd just write a formula for the sum of the squares of the errors and ask the solver to find values for a, b and c that would minimise it.

 

[Special K]

When I have the data organized as in your example (coefficient values are in A1:C9 and the independent values in D1:D9), and then enter the following as an array formula (highlight all 3 cells, enter formula, then CTRL+SHIFT+ENTER) in A12:A14:

=LINEST(D1:D9,A1:C9,FALSE

I get 0.090887382 in all 3 cells A12:A14. Am I entering the formula wrong?

 

[pgc01]

I get 0.090887382 in all 3 cells A12:A14. Am I entering the formula wrong?

Yes, the result is a horizontal vector, not a vertical one.

Instead of A12:A14 try for ex. A12:C12.