How to Solve Simultaneous Equations in Excel

Written by james highland| 13/05/2017

Simultaneous equations, or multivariate algebra, involves relationships between multiple unknown variables. The same number of equations must exist for a certain quantity of unknowns. For example, three equations are necessary to properly solve a scenario involving three unknown variables (See References 1). While this form of computer algorithm often requires specific mathematics software, Microsoft Excel provides some functionality for solving simultaneous equations. The methods are complex and they use some of the most advanced functions within Excel. Once learnt, however, it opens doors to possibilities in Excel that most users never utilise.

Re-state all the simultaneous equations so the variables and constants are in the same place. For example, in a two variable set of equations, arrange each equation in the format of "a + b = c" where "c" is a number and "a" and "b" may have constant multipliers, such as "3a - 4b = 12" (See References 1).

Convert the formulas into a spreadsheet format, with each variable and the resulting numerical constant having its own column. Each cell in a column displays the constant multiplier of the variable. For example, the formula "3a - 4b = 12" would be entered into row 1 of Excel with columns A through C containing "3", "-4", and "12", respectively. If there are two unknown variables, the spreadsheet will contain two rows in this format.

Select a four-cell square in an empty area of the spreadsheet. For example, click on cell G10 with the mouse and keep the left mouse button depressed while dragging down one row and to the right one column until four cells are selected.

Type "=MINVERSE(A1:B2)" while the four cells are selected. Do not press Enter when finished.

Press and hold the Control button on the keyboard. While held, press and hold the Shift button. While both buttons are held, press the Enter key, then release all buttons. This formula entry process is different than merely pressing Enter for most other formulas. It is necessary when interacting with an array of data.

Select a two-cell vertical range. Click in any empty cell and drag down one cell.

Type "=MMULT(G10:H11,C1:C2)" and press the Control, Shift, and Enter keys together as done previously. The two-cell range will now display two numbers. These numbers equate to the two unknown variables in the equation. The variable "a" solves to the number displayed in the first cell. The other unknown, "b", is the solved in the second beneath it.