If you have a system of linear equations, you can quickly solve it with Linear Algebra using the Matrix formulas in Excel. This works in the Pearson Vue environment as well, so it can be a handy shortcut on the CBT exam.
Types of CAS Problems with Systems of Equations
- Exam 9 – Solving for factors in a Multifactor APT Model (BKM Ch. 10)
- Exam 8 – Solving for factors in Multi-Dimensional Credibility (Couret & Venter)
Key Excel Formulas
- MINVERSE ( Array A ) – Returns the inverse of A
- MMULT ( Array A, Array B) – Multiplies matrices A and B
EXAMPLE
Solve the system of linear equations below for unknown variables a, b and c:
- 2a + 12b + 4c = 25
- 3b + 12c = 9
- 8a + 6b + 11c = 32
SOLUTION
In matrix notation, this system of equations is A * X = B where:
- A = Matrix of coefficients
- X = The unknown variables (solution vector): [a, b, c]
- B = The vector of constants: [25, 9, 32]
In Linear Algebra, the solution for the unknown variable vector is:
X = A-1 x B
where A-1 is the inverse of A
Below are the steps to solve in Excel:
STEP 1: CALCULATE THE INVERSE OF A
Use MINVERSE(array) to calculate the inverse of A.
This is an array formula, so select the output range (E7:G9), enter the formula and then press CTRL + SHIFT + ENTER.
STEP 2: CALCULATE THE SOLUTION VECTOR X BY MULTIPLYING A_INVERSE AND B
Use MMULT(array A, array B) to calculate the solution vector X as the product of A_inverse and B.
Again, this is an array formula: Select the output range (E11:E13), enter the formula and then press CTRL + SHIFT + ENTER. Note, you don’t add the curly brackets in the formula. Excel does that.
EXCEL SPREADSHEET WITH MORE EXAMPLES
Download the Excel spreadsheet below for a quick reference. It also has two practice problems for Exam 8 and Exam 9:
- Exam 9 – RF BKM 10-1: Multi-factor APT Model (from the Exam 9 Problem Pack)
- Exam 8 – Multi-Dimensional Credibility recipe: Couret & Venter (from the Exam 8 Cookbook)
System of Equations Spreadsheet
One final note: Try it out for yourself on the spreadsheet problem on the Pearson Vue Demo site to make sure you’re comfortable with it.