This example will show how to solve a system of linear equations in Excel. For example, we have the following system of linear equations:
| 5x | + | 1y | + | 8z | = | 46 |
| 4x | — | 2y | = | 12 | ||
| 6x | + | 7y | + | 4z | = | 50 |
In matrix representation, it can be written as AX=B.
| 5 | 1 | 8 | x | 46 | |||||
| At A= | 4 | -2 | 0 | , | X= | y | , | B= | 12 |
| 6 | 7 | 4 | z | 50 |
If А-1 (reverse A) exists, we can multiply both sides by А-1, To obtain X=A-1B. To solve this system of linear equations in Excel, follow these steps:
- Use the function MINVERSE (NBR) to return the inverse matrix А. Select a range first B6:D8. Then paste the function MINVERSE (INV) as shown below and press Ctrl + Shift + Enter.
=MINVERSE(B2:D4)=МОБР(B2:D4)
Note: The formula bar shows that the cells contain an array formula. This means that you will not be able to delete any one of the results, only all at once. To remove all results, select a range B6:D8 and press the key Delete.
- Use the function MMULT (MULTI) to return the product of a matrix A-1 и B. Select a range first G6: G8. Then paste the function MMULT (MULTI) shown below and press Ctrl + Shift + Enter.
=MMULT(B6:D8,G2:G4)=МУМНОЖ(B6:D8;G2:G4)
- Connect the results. Highlight a range G6: G8. Insert a generalized formula (shown below) and click Ctrl + Shift + Enter.
=MMULT(MINVERSE(B2:D4),G2:G4)=МУМНОЖ(МОБР(B2:D4);G2:G4)
