Saturday, February 24, 2018

Cramer’s Rule Two Equations and Two Variables in Excel

Cramer’s Rule Two Equations and Two Variables in Excel

Question:   Consider the system of equations

2x+ 5y=10

7x-6y=-5

Discuss how this system is solved using Cramer’s Rule

Find the (x, y) pair solution for this two-equation two variable system.

Solve using Cramer’s rule with the MDETERM function in Excel.

Create a spreadsheet that can be easily modified for a system of two equations and two variables.

Discussion of Cramer’s Rule:

Cramer’s rule states that x and y are the ratio of two determinants.

The denominator of both x and y from Cramer’s rule is the determinant of the matrix composed of the x and y coefficients.   We denote this determinant D.

 Matrix for denominator of X and Y 2 5 7 -6

The numerator of x from Cramer’s rule is the determinant of the matrix where the constant terms are inserted in the left column and the y coefficients are in the right column.  We denote this matrix Dx
 Matrix for Numerator of X 10 5 -5 -6

The numerator of y from Cramer’s rule is the determinant of the matrix where the constant terms are in the right column and the coefficients of the x variables are in the left column.   We denote this matrix Dy is

 Matrix for Numerator of Y 2 10 7 -5

Discussion of Spreadsheet Solution for this Problem:

The Excel function MDETERM(array) was used to calculate D, Dx and Dy.   See chart below
 Matrix Array Input Solution from MDETERM Function D 2 5 -47 7 -6 Dx 10 5 -35 -5 -6 Dy 2 10 -80 7 -5 X (Dx/D) 0.745 Y (Dy/D) 1.702

The spreadsheet has an input section for coefficients of x, y and the constant term.  Change the values of the spreadsheet to solve any system with two equations and two variables.

Remember there is no solution when the determinant of the coefficient matrix is 0.