11-Matrices VU

LECTURE 11
MATRICES

OBJECTIVES
The objectives of the lecture are to learn about:
• Review Lecture 10
• Matrix functions in Excel
• Set up and manipulate ratios.
• Allocate an amount on a prorata basis using proportions.
MATRIX FUNCTIONS IN MS EXCEL

The Matrix Functions in Microsoft Excel are as follows:

1. MINVERSE
2. MDETERM
3. MMULT

MINVERSE
Returns the inverse matrix for the matrix stored in an array.
Syntax
MINVERSE(array)
array is a numeric array with an equal number of rows and columns.
Remarks
• Array can be given as a cell range, such as A1:C3; as an array constant,
such as {1,2,3;4,5,6;7,8,9}; or as a name for either of these.
• If any cells in array are empty or contain text, MINVERSE returns the
#VALUE! error value.
• MINVERSE also returns the #VALUE! error value if array does not have an
equal number of rows and columns.
• Formulas that return arrays must be entered as array formulas.
• Inverse matrices, like determinants, are generally used for solving systems of
mathematical equations involving several variables. The product of a matrix
and its inverse is the identity matrix — the square array in which the diagonal
values equal 1, and all other values equal 0.
• As an example of how a two-row, two-column matrix is calculated, suppose
that the range A1:B2 contains the letters a, b, c, and d that represent any
four numbers. The following table shows the inverse of the matrix A1:B2.

Column A Column B

Row 1 d/(a*d-b*c) b/(b*c-a*d)

Row 2 c/(b*c-a*d) a/(a*d-b*c)

• MINVERSE is calculated with an accuracy of approximately 16 digits, which
may lead to a small numeric error when the cancellation is not complete.
• Some square matrices cannot be inverted and will return the #NUM! error
value with MINVERSE. The determinant for a noninvertable matrix is 0.

80
© Copyright Virtual University of Pakistan
11-Matrices VU

MINVERSE-EXAMPLE
Find the inversion or multiplicative inverse of following matrix
⎡ 4 −1⎤
⎢2 0 ⎥
⎣ ⎦
The excel formula in the example must be entered as an array formula. Otherwise a
single result, 0, will appear.

Please note that formulas that return arrays must be entered as array formulas.

The steps of finding multiplicative inverse of above matrix is as follows:

1. Enter data of array to be inverted, in Cells A4:B5. ( i-e in cells A4, B4,
A5, B5)
2. Click on cell A6.
3. Keeping left mouse button pressed, drag it to cell B7.Four cells A6, B6, A7, B7 will
be selected.
4. Press F2 from your keyboard. (Key F2 is selected to enter Edit Mode in the active
cell. It’s a keyboard shortcut. Even if you don't press F2, you can write the
formula).
5. Type the formula =MINVERSE(A4:B5). This will appear in cell A6.
6. Press Ctrl, Shift, Enter keys simultaneously from your keyboard.
7. Multiplicative inverse of the matrix will appear in cells A6, A7, B6, B7.

Now click any cell among A6:B7, in the formula bar you can see curly brackets round the formula.
That is, {=MINVERSE(A4:B5)}. This shows that you have gone through the right procedure of
entering array formula.

81
© Copyright Virtual University of Pakistan
11-Matrices VU

MDETERM
Returns the matrix determinant of an array.
Syntax
MDETERM(array)
array is a numeric array with an equal number of rows and columns.
Remarks
• Array can be given as a cell range, for example, A1:C3; as an array constant,
such as {1,2,3;4,5,6;7,8,9}; or as a name to either of these.
• If any cells in array are empty or contain text, MDETERM returns the
#VALUE! error value.
• MDETERM also returns #VALUE! if array does not have an equal number of
rows and columns.
• The matrix determinant is a number derived from the values in array. For a
three-row, three-column array, A1:C3, the determinant is defined as:
MDETERM(A1:C3) = A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)
• Matrix determinants are generally used for solving systems of mathematical
equations that involve several variables.
• MDETERM is calculated with an accuracy of approximately 16 digits, which
may lead to a small numeric error when the calculation is not complete. For
example, the determinant of a singular matrix may differ from zero by 1E-16.

82
© Copyright Virtual University of Pakistan
11-Matrices VU

MDETERM-EXAMPLE
The example shows an array of dimension 4 x 4 in cell range A14:D17. The
formula was entered in cell A18. The result of this calculation is 88.

There are other ways also for using this function. For example you can enter the
matrix as an array constant.

=MDETERM({3,6,1;1,1,0;3,10,2}) Determinant of the matrix as an

array constant (1)

You can calculate the determinant of the matrix in the array constant.

=MDETERM({3,6;1,1}) Determinant of the matrix in the array

constant (-3)

Unequal number of rows and columns results in an error.

=MDETERM({1,3,8,5;1,3,6,1}) Returns an error because the array

does not have an equal number of
rows and columns (#VALUE!)

MMULT
Returns the matrix product of two arrays. The result is an array with the same
number of rows as array1 and the same number of columns as array2.
Syntax
MMULT(array1,array2)
Array1, array2 are the arrays you want to multiply.
Remarks
• The number of columns in array1 must be the same as the number of rows in
array2, and both arrays must contain only numbers.
• Array1 and array2 can be given as cell ranges, array constants, or
references.

83
© Copyright Virtual University of Pakistan
11-Matrices VU

• If any cells are empty or contain text, or if the number of columns in array1 is
different from the number of rows in array2, MMULT returns the #VALUE!
error value.
• The matrix product array a of two arrays b and c is:

where i is the row number, and j is the column number.

• Formulas that return arrays must be entered as array formulas.
MMULT-EXAMPLE
Let
⎡1 3⎤ ⎡ 2 0⎤
A= ⎢ ⎥ , B= ⎢ 0 2 ⎥ . Find AB
⎣7 2⎦ ⎣ ⎦
To find the product AB, follow these steps.
1. Enter array1 in cell range A25:B26. and array2 in cell range D25:E26.

2. Find the dimension of AB matrix. Here as A and B are 2×2 matrices so AB is also a 2×2
matrix.
3. Click on cell A29.
4. Keeping left mouse button pressed, drag it to cell B30.Four cells A29, B29, A30, B30 will
be selected.
5. Press F2 from your keyboard. (Key F2 is selected to enter Edit Mode in the active cell.
It’s a keyboard shortcut. Even if you don't press F2, you can write the formula).
6. Type the formula =MMULT(
7. Select array1.
8. Put comma.
9. Select array2.
10. Close bracket
11. Press Ctrl, Shift, Enter keys simultaneously from your keyboard.
12. Product AB will appear in cells A29, A30, B29, B30.

84
© Copyright Virtual University of Pakistan
11-Matrices VU

RATIO

A Ratio is a comparison between things. If in a room there are 30 men and 15

women then the ratio of men to women is 2 to 1. This is written as 2:1 and read
as “two is to one”. “:” is the notation for a ratio.
Be careful, order matters. A ratio of 2:1 is not the same as 1:2.
In the form of fraction, we can write 2:1 as 2/1.

The method of calculating ratios is as under:

1. Find the minimum value
2. Divide all the values by the smallest value.

In the above example, the smallest value was 15. Division gives 30/15 = 2 for
men and 15/15 = 1 for women. The ratio is therefore 2:1 for men and women.

RATIO-EXAMPLE
Three friends Ali, Fawad and Tanveer are doing business together. To set up
the business Ali invested Rs. 7800, Fawad Rs. 5,200 and Tanveer Rs. 6,500
What is the ratio of their investments. As discussed above the
smallest value is 5200Rs. All values are divided by 5200. The results are 1.5
for Ali, 1 for Fawad and 1.25 for Tanveer. The answer is:
1.5 : 1 : 1.25.

This example can be solved in Excel. The formula is as under:

Cell D57: =B57 / B58
Cell D58: =B58 / B58
Cell D59: =B59 / B58
The result for cell D59 is shown in cell D60, because the cell D59 is used to
display the formula.

PROPORTION

A proportion is an equation with a ratio on each side. It is a statement that two ratios are equal.

85
© Copyright Virtual University of Pakistan
11-Matrices VU

3:4 = 6:8 OR 3/4 = 6/8 is an example of a proportion.

When one of the four numbers in a proportion is unknown, cross products may be used to find the
unknown number. This is called solving the proportion or ESTIMATING USING RATIO.

EXAMPLE
Ratio of sales of Product X to sales of Product Y is 4:3. The sales of product X is
forecasted at Rs. 180,000. What should be the Sales of product Y to maintain the
ratio of sales between the two products.

CALCULATION
Ratio sales X : Y = 4 : 3
Insert the value for forecasted sale for X.
180,000 : Y = 4 : 3
It can be rewritten as:
180,000/Y = 4/3
Cross – multiply
180,000 x 3 = 4 x Y
Rewrite to bring the unknown to the left of the equality
4 x Y = 180,000 x 3
Solve
Y = (180,000 x 3)/4
Y = 135,000 Rs

Calculations using EXCEL

In cells B70 and B71 the ratios of Product X and Y were entered.
The value of forecast of product X was entered in cell D70.
Before writing down the formula in excel, it was derived as follows:
1 Ratio of X = (cell B70)
2 Ratio of y = (cell B71)
3 Sale of X = (cell D70)
4 Sale of Y = (cell D71)
Now Ratio X: Y = (cell B70)/ (cell B71)
Ratio of sales = (cell D70)/ (cell D71)
Cross-multiply.
(cell B70) x (cell D71) = (cell B71) x (cell D70)
Cell D71 is unknown. Hence:
(cell D71) = (cell B71) x (cell D70)/ (cell B70)
Or
(cell D71) = (cell B71)/ (cell B70) * (cell D70)
Thus the formula was:
=B71/B70*D70
Please note that actually we are using the ratio Y to X as it is easier to think of ratio of
unknown to the known.

86
© Copyright Virtual University of Pakistan
11-Matrices VU

87
© Copyright Virtual University of Pakistan
11-Matrices VU

ESTIMATING USING RATIO-EXAMPLE 2

In a 500 bed hospital there are 200 nurses and 150 other staff. If the hospital extends
by a new wing for 100 beds, then what additional staff is needed? Let
500 beds B1 and 100 beds B2. Staff nurses N1 is 200 and other staff O1 is 150. What
is the value of N2 and O2 for B2. Obviously the ratio
of beds will be used. As pointed out above, think of the ratio of unknown to known. In
other words ratio B2:B1 or B2/B1. Ratio of nurses would be N2/N1. Ratio of other staff
would be O2/O1.
Now :
N2/N1 = B2/B1 Or N2 = (B2/B1)*N1 or N2 = (100/500)*200 = 40 Nurses
O2/O1 = B2/B1 Or N2 = (B2/B1)*O1 or O2 = (100/500)*150 = 30 other staff.

Calculation
Beds : Nurses : Other staff
500 : 200 : 150
100 : X? : Y?
Nurses
500 : 200 = 100 : X
500 X = 200 x 100
X = (200 x 100)/500 = 40
Other staff
Y = (150 x 100)/500 = 30

Calculation using EXCEL

The calculation using EXCEL was done in a similar fashion as the previous example.

The calculation is self-explanatory.

ESTIMATING USING RATIO-EXAMPLE 3

A Fruit Punch recipe requires mango juice, apple juice and orange juice in the ratio of
3:2:1. To make 2 liters of punch calculate quantity of other ingredients
Again we shall use the ratio of unknown to the unknown. The unknowns are mango
and apple juice. Consider first ratio of required mango juice (3) to total quantity of
punch (6). This was calculated from 3+2+1. Now the quantity of required mango for 2
liter would simply be (3/6)*2. Similarly the required quantity of apple juice is (2/6)*2.

88
© Copyright Virtual University of Pakistan
11-Matrices VU

Calculation
Mango juice : Apple juice : Orange juice
3 : 2 : 1
Total = 6
X? : Y? : Z?
Total = 2 litre
Mango juice (X) = (3 / 6) * 2 = 1 litre
Apple juice (Y) = (2 / 6) * 2 = 0.67 litre
Orange juice (Z) = (1 / 6) * 2 = 0.33 liter

Calculation using EXCEL

Here also the similar ratios were used.
Mango = B20/B23*D23
Apple = B21/B23*D23
Orange = B22/B23*D23

89
© Copyright Virtual University of Pakistan