0

I am having some trouble finding a solution for a system of equations using excel.
The system is of the form Ax=b, with A a matrix and x and b vectors. Obviously, the goal is to find x.

The system does not necessarily have the same number of equations and unknowns. An exact solution is not always possible. Therefor I want to find the best approximation, possibly using least squares.

Are there standard methods in Excel to do that?
If not, can someone provide me with a macro that does that or explain how I can write my own?

Thanks

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
neXus
  • 2,005
  • 3
  • 29
  • 53
  • Yes I do. Unfortunately, my supervisor told me to use excel. I am not very happy with that choice. Can I somehow run MathLab code from within Excel or vba? – neXus Sep 09 '10 at 11:33

2 Answers2

3

Check the LINEST() function.

kennytm
  • 510,854
  • 105
  • 1,084
  • 1,005
  • This seems like the solution to my problem. However, I can't get it to work. I'll try to make it work and if it turns out okay, I'll accept your answer. – neXus Sep 09 '10 at 16:48
0

You can use the pseudoinverse and Excel's matrix functions. Suppose there is a named range _A containing your matrix, and another named range _b containing your vector, then you can select an appropriately shaped set of cells and enter the formula:

=MMULT((MINVERSE(MMULT(TRANSPOSE(_A); _A))); MMULT(TRANSPOSE(_A); _b))

Remember to press Control-Shift-Enter to enter this formula as it is an array formula. It will appear with braces around it when you select the cells.

chthonicdaemon
  • 19,180
  • 2
  • 52
  • 66