I have a system of 22 linear equations (exactly 22 equations and 22 unknowns) which are dynamically generated in an Excel spreadsheet. Is there a way to have Excel solve the system and produce a value for each of the unknown variables?
Another SO question has a lot of good information about the algorithms used to solve such equations. Cramer's rule in particular looks promising, but I'm not sure how to implement it in Excel.
Any help is appreciated!
This page gives the following simple method:
Enter the coefficient matrix in an nxn range.
Enter in the vector of constants in a n-tall column. Select a blank n-tall column. It is important that you not have only one cell selected.
Type, =MMULT(MINVERSE(coefficients_matrix_range), constants_vector_range) in the formula text box and press [CTRL][SHIFT][ENTER]. It is important that you not simply press [ENTER].
This should work adequately, since you only have a small system (22x22). While, ideally, you wouldn't invert the matrix directly, it shouldn't matter in this case.
You definitely don't want Cramer's rule. Due to its fairly poor numerical properties, it's more of theoretical than practical interest.
You should not ever consider Cramer's rule to solve a linear system! It is so inefficient it is impossible to carry out for even small systems.
Excel is not the right tool to solve a linear system. If I were you I'd write a simple python script that reads the excel file, solve the equation (with numpy) and save the result in a file that excel can read. (even better, avoid Excel altogether if you can).
The Excel LINEST function should do it - and will return better results than MMULT(MINVERSE(...)...) in many cases.
You might also find the LOGEST, GROWTH and TREND functions useful.
If you have any problems with in the inverse matrix calc, e.g. over / under flow / truncation etc, you could always use the Solver - else get a copy of numerical recipes in C and translate into VBA.
What about Microsoft's Solver Foundation? I seem to remember there's some example for a smaller set of simultaneous equations in the documentation that comes with the express version.