Spreadsheet #21:  Using Matrix Operations for Solving Series of Equations - Cobalt, Nickel, and Copper

[Back to Group 1 Assignments] [Back to Chem3020 home]

Matrix operations are exceptionally easy to perform with spreadsheets.  Some review may be necessary to remember matrix operations, but there are three easy steps to solving n equations with n unknowns:

Consider a series of equations, followed by its determinant matrix and the constant matrix:

Equations	Determinant Matrix	Constant Matrix
2x +  y  -   z  =  0	2   1  -1	0
x -   y  +  z  =  6	1 -1   1	6
x + 2y +  z  =  3	1   2   1	3

Solving for x, y and z is simple with a spreadsheet;  Invert the determinant matrix, then multiply by the matrix of constants.  The three results are the values of x,y, and z.  Consider the following example solution and how it might look on your spreadsheet: 

.	Constants	x-Coef:	y-Coef:	z-Coef:
Eqn#1	0	2.0	1.0	-1.0
Eqn#2	6	1.0	-1.0	1.0
Eqn#3	3	1.0	2.0	1.0
.	.	Invert Matrix to Yield:
.	Inverted Matrix:	0.33	0.33	0.00
.	.	0.00	-0.33	0.33
.	.	-0.33	0.33	0.33
.	.	Multiply Inverted Matrix Above by Constants Matrix to Yield  Solution:
.	Solution:	x=	2.0	.
.	.	y=	-1.0	.
.	.	z=	3.0	.

Matrix commands are different in each spreadsheet, requiring your to study your own software to accomplish the two commands required for this operation: "invert matrix" and "multiply matrix." 

Determine the concentrations of cobalt, nickel, and copper from the following data. Absorbance data was collected at three different wavelengths.  In separate experiments, the molar absorbtivity coefficients (units of moles/Liter) were determined for pure solutions of each of these metals at each of these wavelengths.   Using this data, three equations may be written:

Absorbance @ 394nm  =  0.995 [Co] + 6.868 [Ni] +   0.188 [Cu]

Absorbance @ 510nm  =  6.450 [Co] + 0.215 [Ni] +   0.198 [Cu]

Absorbance @ 808nm  =  0.469 [Co] + 1.179 [Ni] + 15.052 [Cu]

Using matrix operations in your spreadsheet software, find the concentrations of Co, Ni, and Cu.  (Click to download data:  21CoNiCu .)

Absorbance Data Matrix		Constants Matrix (absorbtivity coefficients)
Unk. Mixture		Molar Absorbtivity (1/M.cm)
Wavelength	(Absorbance)	Cobalt	Nickel	Copper
394	0.845	0.995	6.868	0.188
510	0.388	6.450	0.215	0.198
808	1.696	0.469	1.179	15.052
.
.	Inverted Matrix:	.	.	.
.	.	.	.	.
.	.	.	.	.
.
.	Solution Matrix:	Co=	.	M
.	.	Ni=	.	M
.	.	Cu=	.	M

Excel Programming Tips:
Excel command:	Result
=MINVERSE(Array range)	Takes the Inverse of the array provided
=MMULT(Array 1, Array 2)	Multiplies two arrays together.
When using Matrix commands in Excel first select (highlight) the area that will be filled. After typing the formula into the upper left cell of this range, you must use Ctrl+Shift+Enter to fill the selected area.
Remember that matrices follow certain rules such as: Matrices do not commute multiplicatively.