Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

This section describes how to use formulas to solve simultaneous linear equations. The following is an example of a set of simultaneous linear equations:

3x + 4y = 8 4x + 8y = 1

Solving a set of simultaneous equations involves finding the values for x and y that satisfy both equations. For this set of equations, the solution is as follows:

x = 7.5 y = -3.625

The number of variables in the set of equations must be equal to the number of equations. The preceding example uses two equations with two variables. Three equations are required to solve for three variables (x, y, and z).

The general steps for solving a set of simultaneous equations follow. See Figure 10-3, which uses the equations presented at the beginning of this section.

1. Express the equations in standard form. If necessary, use simple algebra to rewrite the equations such that the variables all appear on the left side of the equal sign. The two equations that follow are identical, but the second one is in standard form:

   3x -8 = -4y 3x + 4y = 8

2. Place the coefficients in an n x n range of cells, where n represents the number of equations. In Figure 10-3, the coefficients are in the range I2:J3.

3. Place the constants (the numbers on the right side of the equal sign) in a vertical range of cells. In Figure 10-3, the constants are in the range L2:L3.

4. Use an array formula to calculate the inverse of the coefficient matrix. In Figure 10-3, the following array formula is entered into the range I6:J7. (Remember to use Ctrl+Shift+Enter to enter an array formula.)

   {=MINVERSE(I2:J3)}

5. Use an array formula to multiply the inverse of the coefficient matrix by the constant matrix. In Figure 10-3, the following array formula is entered into the range J10:J11. This range holds the solution.

   {=MMULT(I6:J7,L2:L3)}

Figure 10-3: Using formulas to solve simultaneous equations.