Transcript Using Excel array formulas to take a matrix

Use’s Excel Array Formulas to
Solve Simultaneous Equations
PHY 202 (Blum)
1
Simultaneous Linear Equation
PHY 202 (Blum)
2
Present Example

The example we will consider here is
from resistor circuits and goes by the
name Kirchhoff’s circuit laws.



The variables are currents in different
“loops” of the circuit.
The coefficients (numbers multiplying the
variables) are resistances.
The constants are voltages.
PHY 202 (Blum)
3
Example with numbers




5
0
0
= 3.2 JA
= -1.0 JA
= -2.2 JA
– 1.0 JB
+ 5.5 JB
– 3.0 JB
- 2.2 JC
– 3.0 JC
+ 6.9 JC
In the above we have three current
variables: JA, JB and JC.
PHY 202 (Blum)
4
Loop equations as matrix equation
5 = 3.2
JA – 1 JB - 2.2 JC
 0 = -1 JA + 5.5
JB – 3 JC
 0 = -2.2JA – 3 JB + 6.9 JC

 3.2  1  2.2  J A  5
  1 5.5  3   J   0

 B   
 2.2  3 6.9   J C  0
5
PHY 202 (Blum)
Enter matrix in Excel, highlight a region the
same size as the matrix.
6
PHY 202 (Blum)
In the formula bar, enter =MINVERSE(range)
where range is the set of cells corresponding to
the matrix (e.g. B1:D3). Then hit Crtl+Shift+Enter
7
PHY 202 (Blum)
Result of matrix inversion
8
PHY 202 (Blum)
Prepare the “voltage vector”, then highlight a range the same size as the
vector and enter =MMULT(range1,range2) where range1 is the inverse
matrix and range2 is the voltage vector. Then Ctrl-Shift-Enter.
Voltage vector
9
PHY 202 (Blum)
Results of Matrix Multiplication
10
PHY 202 (Blum)
The answer
The current vector is the answer
 JA=3.152 mA
 JB=1.470 mA
 JC=1.644 mA

PHY 202 (Blum)
11
Use the matrix approach in
Excel to find the solution for



10 = 7 JA
0 = -3 JA
0 = -4 JA
PHY 202 (Blum)
– 3 JB
+ 6 JB
– 1 JB
- 4 JC
– 1 JC
+ 10JC
12
Atomic Isotope Problem

Atomic Masses
Isotopes:
The element indium exists naturally as two isotopes.
113In has a mass of 112.9043 amu, and 115In has a
mass of 114.9041 amu. The average atomic mass of
indium is 114.82 amu. Calculate the percent relative
abundance of the two isotopes of indium.

http://eppe.tripod.com/stoictry.htm
PHY 202 (Blum)
13
Use the matrix approach in
Excel to find the solution for

112.9043 x + 114.9041 y = 114.82
x+y=1
where x is the fraction of the first
isotope and x is the fraction of the
second isotope.
PHY 202 (Blum)
14